Symptoms
The SQL database transaction logs are not decreasing in size after being truncated via Application-Aware backup, even though MMS logs show truncation was performed:
2021-10-10T18:54:18:107-05:00 16136 I00000000: service_process(14868): TruncateLogOf: Local881 start
2021-10-10T18:54:18:248-05:00 16136 I00000000: service_process(14868): TruncateLogOf: Local881 done
Cause
The transaction logs do not reduce in size during truncation, as the truncated space is not deallocated.
Solution
To deallocate the free space and successfully truncate the logs, perform a shrink operation in SQL Server Management Studio:
-
Right-click the database and select Tasks > Shrink > Files in the drop-down menu.
-
In the Shrink File page, change the File Type to Log.
-
Select the transaction log file you want to shrink.
-
Under Shrink action, select one of the options:
- Release unused space: Releases unused space to the operating system, thus reducing the file size without moving any data.
- Reorganize pages before releasing unused space: Releases unused space to the operating system while trying to relocate rows to unallocated pages and shrink the log to a predefined value.
-
Empty file by migrating the data to other files in the same filegroup: Moves all data from the selected log file to other files in the same filegroup and attempts to delete the empty file later.
-
Click OK to initiate the shrink operation.