This is a very common scenario and generates quite a few calls for us.
Transaction log file is growing rapidly and has consumed all the disk space on your drive. You don’t have any free disk space left and can no longer perform data modification operations on your database.
Additionally, your database might be marked suspect by SQL Server, as it can no longer expand the transaction log file to record operations.
You are perplexed as to why the log file is growing so rapidly.
If you have autogrow set for your log files, they can expand automatically.
This automatic expansion could be driven because of several factors:
- Uncommitted Transactions
- Extremely Large Transactions
- Operations: DBCC DBREINDEX and CREATE INDEX
- While Restoring from Transaction Log Backups
- Client Applications Do Not Process All Results
- Queries Time Out Before a Transaction Log Completes the Expansion and You Receive False ‘Log Full’ Error Messages
- Un-replicated Transactions
For a detailed discussion on each cause/scenario mentioned above, refer to KB 317375.
To recover from this situation, you should:
- Truncate the inactive portions of your transaction log file
- Shrink the transaction log file
KB article 873235 describes these steps in more details.
POSTED BY : Sanchan Saxena