INF: Cannot Turn Off Transaction Logging

ID: Q59462

The information in this article applies to:

Microsoft SQL Server for OS/2, version 4.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 
Issue type        :

Last Reviewed: March 9, 1999