Why is the LDF database transaction log file huge?


Article ID: 179628


Updated On:


Management Platform (Formerly known as Notification Server)




I have seen an issue where the LDF file grows to consume all free disk space even when it is then manually truncated the new day it has grown again.

The reason, in this case, that the LDF file grew was a long running transaction that was never commited. Purging tasks are often the culprit of this. When a table grows excessively large, several million rows, the purge processes may try and trim it down but fail due to the size leaving a large uncommitted transaction explaining the growth in the LDF file.

To troubleshoot this

1 - Check for any abnormally large table sizes using the details in AKB 21310.

 2 - Determine whether it is safe to truncate the table manually. If the table contains something like historical event information that you no longer need, for example reporting, then it should be safe to truncate but consider this carefully. The SQL syntax to truncate is truncate table 'tablename' 

3- Use the details in AKB 18204 to manually shrink the log

Note: If the log file point to purging failing specifically for monitor solution full details on how to correct this are in AKB 46925