Manage Log file size

Manage SQL Log-File Size

If your SQL transaction log-file is taking lots of space, follow the steps as shown below to bring down the size of the log file.

Check For Disk Usage
The first step is to carry out a check to find out how much of disk space is being used by the transaction log file.

1. In the SQL Server Management Studio, right click on the BizTalk360 database name and select Reports > Standard Reports > Disk Usage

2. Verify the transaction log space usage. If you find a huge percentage of unused space, you need to shrink the size of the transaction log file to release unallocated space. Refer Section 2 for the process of shrinking the transaction log file size.

Shrink The Transaction Log File Size
To reduce the size of the transaction log file size, follow the steps as shown below:

• In the SQL Server Management Studio, right click on the BizTalk360 database name and select Tasks > Shrink > Files
• Select the file type and file name
• Select the Release unused space radio button. Selecting this option causes any unused space in the file to be released to the operating system and shrinks the file to the last allocated extent. This reduces the file size without moving any data.
• Click OK

You can also create a SQL script for shrinking the transaction log file and execute the script.

Even after performing the above steps, if you are not able to truncate the size of the log , it means the transaction log grows to be inordinately large on a database that's in FULL or BULK_LOGGED recovery mode. This happens after a database backup has been taken, which switches the log into a mode where it won't truncate. In these circumstances, if you do not take a transaction log backup, the log file size will continue to grow. Issue the following commands in the SQL Server Management Studio and then try to shrink the log file size:

Use BizTalk360
SELECT log_reuse_wait_desc FROM sys.databases WHERE NAME = 'BizTalk360';

If the above query returns the result as LOG_BACKUP, issue the following command:
BACKUP LOG BizTalk360 WITH NO_LOG;

Then once again issue the command:
SELECT log_reuse_wait_desc FROM sys.databases WHERE NAME = 'BizTalk360';

From SQL server 2008, the below command has been Discontinued. The transaction log is automatically truncated when the database is using the SIMPLE recovery model. If you must remove the log backup chain from a database, switch to the simple recovery model.

Please find the steps to clean the database in Availability group:

• Remove the database from the Availability group as it requires "full recovery" model
• When the database is disconnected from Availability Group, change the recovery model to SIMPLE
• Then, run the cleaning job to shrink and clear the database
• Next, delete the database that was disconnected on the 2nd server
• Take a full backup of the cleaned DB and change back to FULL recovery again
• Next, add the database to the Availability group so it gets synced again