Why is my transaction log file growing rapidly? How do I stop it from eating up all my disk space?

  • Article

This is a very common scenario and generates quite a few calls for us.

SCENARIO

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.

CAUSE

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.

RESOLUTION

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