INF: Cannot Turn Off Transaction Logging

Last reviewed: April 25, 1997
Article ID: Q59462

The information in this article applies to:

  - Microsoft SQL Server version 4.2 for OS/2

To be able to effectively manage transactions, transaction logging is always required. Without transaction logging, a transaction could not be rolled back. Even a single UPDATE statement that affected multiple rows could not be trusted to complete in an atomic fashion or even at all if it were not being logged.

For this reason, there is no way to turn off transaction logging, although bulk copy operations (BCP or SELECT INTO) can bypass logging if you set DBOPTION 'SELECT INTO' to true.

If you want to avoid the need to dump your transaction log, set the TRUNCATE LOG ON CHECKPOINT option. By using this option, you can keep the log at a minimum size; however, the log still must be large enough to be able to roll back your largest single transaction. If you perform a single update that affects many rows (or an entire table), the log can still be very large indeed. If you encounter this situation, you may be able to rewrite an UPDATE statement to multiple UPDATE statements that each qualify only a subset of the rows. (For example, where rows between 1 and 50000, then do another udate where rows between 50001 and 100000.)

Note: There is a down side of using TRUNCATE LOG ON CHECKPOINT.   Since
you are not maintaining the logs, you also are not able to do incremental recovery. That is, you cannot recover except to the last dump database. You also cannot recover from transaction dumps.


Additional query words:
Keywords : kbother SSrvServer
Version : 4.2
Platform : OS/2


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.