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

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

• Anonymous
  May 29, 2009
  PingBack from http://paidsurveyshub.info/story.php?title=microsoft-sql-server-support-blog-why-is-my-transaction-log-file

• Anonymous
  May 12, 2015
  Very useful

• Anonymous
  January 01, 2017
  fantastic article! This is so chock full of users information and the resources you provided was helpful to me. There I found a very informative blog Preventing Transaction Log Files from Growing Unexpectedly. You may also have a look:http://www.sqlmvp.org/transaction-log-is-too-big-or-growing-unexpectedly/