ID Number: Q72783
1.00 1.10 1.11 4.20
OS/2
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