Microsoft KB Archive/72783

{|
 * width="100%"|

INF: Recoverability and Transaction Log Dumps

 * }

Q72783

-

The information in this article applies to:


 * Microsoft SQL Server for OS/2, version 4.2
 * Microsoft SQL Server version 4.2x

-

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

Issue type :

Technology : kbSQLServSearch kbAudDeveloper kbZNotKeyword3 kbSQLServ420OS2