Microsoft KB Archive/59462

= INF: Cannot Turn Off Transaction Logging =

Article ID: 59462

Article Last Modified on 2/14/2005

-

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

-



This article was previously published under Q59462



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, plus BULK INSERT in SQL Server versions 7.0 and later) 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 update 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.

Keywords: kbother KB59462

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.