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.
1. 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.
- In the SQL Server Management Studio, right click on the BizTalk360 database name and select Reports > Standard Reports > Disk Usage
- 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.
2. 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
Even after performing the above steps, if you are not able to truncate the size of the log file size, it could be because 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 until it's been backed up. 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:
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:
Then once again issue the command:
If the query does not return any value, then try to shrink the log file size as described in Section 2.