INF: Recoverability and Transaction Log Dumps

ID: Q72783

The information in this article applies to:

SUMMARY

The following information describes how to preserve recoverability while using the DUMP TRANSACTION command to shrink the transaction log.


MORE INFORMATION

In the past, some Microsoft documentation has advised that after each full database dump, a transaction dump with truncate only should be executed to remove all inactive transactions from the log, thereby reducing its size. The following is a brief explanation of why this could cause problems during database recovery.

Whenever a dump is created, a checkpoint location is established in the transaction log along with a pointer to the last active transaction. Transactions that are currently executing at the time of a database dump will not be preserved, but will only be applied to the database after the dump of the affected data pages has occurred. During recovery, any active transaction that was not completely recorded in the transaction log will be rolled back, removing it from the database.

Subsequently dumping the transaction log with truncate only will remove all inactive transactions from the log, including any that completed after the last database dump was initiated. If you later created a normal transaction dump, all inactive transactions recorded prior to the last active transaction would again be truncated to reduce the size of the log.

Below is an illustration of what can happen if the following is created:

  1. Database dump


  2. Transaction dump with truncate only


  3. Transaction dump


During the recovery process, each dump must be applied in a linear order and transactions are then rolled forward into the database. Because the transaction dump with truncate only (2) does not save the truncated transactions, there can be a logical gap of transactions that were completed between the time the entire database dump was initiated (1) and the time the transaction dump with truncate only (2) occurred. These transactions would never be applied during recovery and could, therefore, lead to data corruption. 

    Last
   Active   Checkpoint
---|--------|--------                                1. Database Dump

                          Last
     Deleted      Active   Checkpoint
xxxxxxxxxxxxxxxxxx|--------|--------                   2. Transaction
                                                          Dump
                                                          (truncate
                                                          only)

                     Saved/      Last
                     Deleted   Active     Checkpoint
xxxxxxxxxxxxxxxxxx|xxxxxxxxxxxx|---------|--------    3. Transaction
                                                         Dump

   Last                                       Last
   Active   Checkpoint          Active   Checkpoint
---|--------|xxxxx|------------|---------|--------       4. Reapplied
                                                          Database
             ^^^^^                                        Dump (1) and
             Missing Transactions                         Transaction
             could lead to data                           Dump (3)
             corruption during
             recovery

After a dump transaction with truncate only, SQL Server will still allow subsequent normal transaction dumps to continue to shrink the log. However, to avoid the problem of potential data corruption, SQL Server records when a transaction dump with the truncate only option has taken place in a database, and will not allow a subsequently saved transaction log to be used to recover the database. This will remain true until a new dump of the database has been produced.

To shrink your transaction log so that recovery will restore all of the database transactions, use either of the following two methods:
  1. DUMP DATABASE, DUMP TRANSACTION, DUMP TRANSACTION, ...


  2. DUMP DATABASE, DUMP TRANSACTION (TRUNCATE), DUMP DATABASE, DUMP TRANSACTION



Keywords          : kbother SSrvGen SSrvServer 
Version           : 4.2 | 4.2
Platform          : OS/2 WINDOWS 
Issue type        :

Last Reviewed: March 11, 1999