INF: Reasons Why SQL Transaction Log Is Not Being Truncated

Last reviewed: April 25, 1997
Article ID: Q62866

The information in this article applies to:

  - Microsoft SQL Server version 4.2 for OS/2
  - Microsoft SQL Server versions 4.2, 4.21, and 4.21a

SUMMARY

Listed below are various 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.


Additional query words: Windows NT
Keywords : kbother SSrvGen SSrvWinNT
Version : 4.2 | 4.2 4.21 4.21a
Platform : OS/2 WINDOWS
Issue type : kbtshoot


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 25, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.