Why is the LDF database transaction log file huge?

book

Article ID: 179628

calendar_today

Updated On:

Products

Management Platform (Formerly known as Notification Server)

Issue/Introduction

 

Resolution

Question
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.

Answer
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