Description of the reasons that the SQL Server transaction log is not being truncated

Article translations Article translations
Article ID: 62866 - View products that this article applies to.
This article was previously published under Q62866
Expand all | Collapse all

SUMMARY

Listed below are some reasons why the transaction log fills up and never gets truncated, even though the "trunc. log on chkpnt" option is set to true and regular checkpoints are done during processing.

MORE INFORMATION

An unbounded update fills up the log; that is, there is no WHERE clause on the UPDATE statement. Since a transaction (either user declared or implicit) must be able to be entirely rolled back or committed as a whole, the log must be large enough to maintain all information for the transaction. Thus, even when truncating the log, it is possible to fill the log if it is too small for a single large transaction.

This situation can also occur if a WHERE clause is used, the table is very large, and the WHERE clause is too general, causing a large number of records to be affected. There is another case where this could happen: if multiple updates are performed that together affect enough records to fill the log before the checkpoint is done, the log can still fill up.

Properties

Article ID: 62866 - Last Review: February 19, 2007 - Revision: 4.3
APPLIES TO
  • Microsoft SQL Server 4.21a Standard Edition
  • Microsoft SQL Server 6.0 Standard Edition
  • Microsoft SQL Server 6.5 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Workgroup Edition
Keywords: 
kbinfo kbother KB62866

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com