You are currently offline, waiting for your internet to reconnect

Your browser is out-of-date

You need to update your browser to use the site.

Update to the latest version of Internet Explorer

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

Extended support for SQL Server 2005 ends on April 12, 2016

If you are still running SQL Server 2005 after April 12, 2016, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

This article was previously published under Q62866
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 onthe UPDATE statement. Since a transaction (either user declared orimplicit) must be able to be entirely rolled back or committed as a whole,the log must be large enough to maintain all information for thetransaction. Thus, even when truncating the log, it is possible to fill thelog if it is too small for a single large transaction.

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

Article ID: 62866 - Last Review: 02/19/2007 21:25:12 - Revision: 4.3

  • 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
  • kbinfo kbother KB62866
Feedback