Fix for SQL Server Log Space (Error : 9002) through PowerShell

-


Scenario:

 Error : 9002 The Transaction Log for database 'DB' is full due to 'LOG_BACKUP'...

One of the most common issue/error faced by developers working with databases i.e., SQL Server, is when LOG size reaches 100% or disk space reaches capacity. Usually this can be resolved by running SQL queries in SSMS to truncate the LOG file, in order words, reducing its size.

Let us dive into a specific situation, suppose, there is only one disk in a development environment and the disk space reaches capacity, no programs can be opened e.g. SSMS or even internet browsers. In such a case, we can either increase the disk space or truncate the LOG file using a system utility program, which require minimal temporary storage on disk to execute. 

One such program is PowerShell and by executing the following commands a quick-fix can be achieved.


Prerequisites:

1. Access to the development environment, where the database is housed.

2. Administrator access.


Process:

On the development environment, open Windows PowerShell as admin. 

To execute the PowerShell commands the hostname will be required.

 

Step 1: Fetch hostname

# To get hostname.
[System.Net.Dns]::GetHostName()
# Or use this
$env:COMPUTERNAME


Step 2: Check log space usage for database logs

From Step 1, enter the relevant hostname. 

# To check log space usage for database logs
# In this case Hostname is "TEST_HOSTNANME".
Invoke-Sqlcmd -ServerInstance "TEST_HOSTNANME" -Query "DBCC SQLPERF (LOGSPACE)"


Step 3: Check Database log details

From Step 2, enter the relevant database name. 

# To check a Database log details (Name, Location, Size).
# In this case Hostname is "TEST_HOSTNANME".
# In this case Database name is "AxDB".
Invoke-Sqlcmd -ServerInstance "TEST_HOSTNANME" -Query "USE AxDB; select * from sys.database_files;"

 

Step 4: Change the database recovery model to SIMPLE

# In order to truncate the log, we need to change the database recovery model to SIMPLE.
# In this case Hostname is "TEST_HOSTNANME".
# In this case Database name is "AxDB".
Invoke-Sqlcmd -ServerInstance "TEST_HOSTNANME" -Query "ALTER DATABASE AxDB SET RECOVERY SIMPLE;"

 

Step 5: Shrink the log file

From Step 3, enter the name of the LOG file.

# Shrink the log file to 1 MB.
# In this case Hostname is "TEST_HOSTNANME".
# In this case Database name is "AxDB" and log file name is "AxDB_UAT_log".
Invoke-Sqlcmd -ServerInstance "TEST_HOSTNANME" -Query "USE AxDB; DBCC SHRINKFILE (AxDB_UAT_log, 1)"

 

Step 6: Reset the database recovery model

# To reset the database recovery model.
# In this case Hostname is "TEST_HOSTNANME".
# In this case Database name is "AxDB".
Invoke-Sqlcmd -ServerInstance "TEST_HOSTNANME" -Query "ALTER DATABASE AxDB SET RECOVERY FULL;"

 

 

Comments

Post a Comment

Popular posts from this blog

How to Deploy a Package from DEV to UAT using LCS

-
Image
Scenario: Suppose you have been tasked to create a deployment package and deploy it to a UAT environment. For a beginner, who has never done a deployment before, the task might seem overwhelming. Regardless, getting it done is what this blog post is for. Consider you are on a cloud-hosted DEV environment for Microsoft Dynamics 365 Finance and Operations. You or some developer has written x++ code, or perhaps created custom entities or it can be any type of customization for D365 FO. Your customizations have been tested and approved to be deployed to UAT (User Acceptance Test) environment. Using Lifecycle Services (LCS) we will deploy your customizations to a UAT environment. As you might be familiar with or know, there are a few different approaches to deploying customization to an environment. This blog post solely covers deployment through LCS.   Prerequisites: To successfully perform the process, you will need: Access to DEV environment, i.e. where your customizations lie. Adm...
Read more

How to Deploy a Package from UAT to PROD using LCS

-
Image
  Scenario: Continuing onward from the previous blog. How to Deploy a Package from DEV to UAT using LCS Your customizations have been successfully deployed to a UAT (sandbox) environment. Functionals and/or users have extensively tested and verified the customization on the UAT environment. You have been given go-ahead and now you need to carry those customizations from UAT environment to production environment. All right then, let’s jump right into it! Prerequisites: To successfully perform the process, you will need: Admin creds or project security role of "Project owner" on LCS. A successfully completed deployment on a UAT (sandbox) environment. Process: Step 1: Mark an application deployable package from a sandbox environment as a release candidate for a production environment. Step 2: Schedule the release candidate on a production environment. Step 1: Mark an application deployable package from a sandbox environment as a release candidate for a production environment. ...
Read more

Power BI Dashboards Built on D365F&O AxDB (For On-Prem Environments)

-
Image
  This blog post is a showcase for Microsoft Power BI Dashboards/Reports developed for Power BI Reporting Server. Meaning, the dashboards/reports have been built using the AxDB database and not AxDW (referring to the Entity store in D365F&O). The dashboards/reports are: Asset Master. Customer Statement. Employee Loans. Government Transactions. Item Requirements. Posted Project Transactions. Project Budget. Project Master. Project WBS. Purchase Agreement. Purchase Order. Purchase Requisition. Trial Balance. Vendor Statement.  1. Asset Master   2. Customer Statement    3. Employee Loans   4. Government Transactions 5. Item Requirements     6. Posted Project Transactions   7. Project Budget   8. Project Master   9. Project WBS 10 Purchase Agreement   11. Purchase Order   12. Purchase Requisition   13. Trial Balance   14. Vendor Statement
Read more