Two database options have an important affect on transaction logs: trunc. log on chkpt. and select into/bulkcopy. The trunc. log on chkpt. option automatically performs a DUMP TRANSACTION database WITH TRUNCATE_ONLY statement every time the SQL Server checkpoint handler wakes up. When trunc. log on chkpt. is set, the checkpoint handler wakes up once a minute for that database. Setting this option for a database is useful primarily in a development environment when backups of the transaction log are not wanted.
Note It is recommended that you use the lazy writer instead of the trunc. log on chkpt. option to flush out batches of dirty, aged buffers and make them available to user processes by placing them in the free buffer pool. (Buffers are considered dirty when they contain changes that must be written back to disk before the buffer can be reused for a different page.)
The lazy writer eliminates the need to checkpoint frequently for the purpose of creating available buffers. Frequent checkpointing creates a drag on server performance.
For more information about the lazy writer, see the Microsoft SQL Server documentation.
When trunc. log on chkpt. is set, you can rely only on recovering from your database dumps, not from your log dumps. When this option is set, SQL Server prohibits a transaction log dump to a physical dump device¾attempting to do so will yield SQL Server error 4208:
DUMP TRANsaction is not allowed while the trunc. log on chkpt. option is enabled: use DUMP DATABASE, or disable the option with sp_dboption.
You will need to dump the entire database after turning off the option. Although subsequent transaction log dumps to physical dump devices are allowed once the option is turned off, you must first dump the entire database before dumping any new transaction logs. Transaction log dumps performed before the database was dumped are not recoverable because the sequence number for the database will have already been incremented for as many times as the checkpoint handler truncated the log.
The following example illustrates the affects of the trunc. log on chkpt. option. Assume that a database was dumped at midnight, the transaction log was dumped to a physical device at 8 A.M., and at 8:15 A.M., the trunc. log on chkpt. option was set for the database. At 8:45 A.M., the option was turned off, and at 9 A.M., the log was successfully dumped to another dump device. Between 8:15 A.M. and 8:45 A.M., there may have been as many as 30 automatic truncations of the transaction log—one truncation each time the checkpoint handler woke up. If this database later needed to be restored, the database dump could be loaded, followed by the 8 A.M. transaction log dump. However, attempting to load the 9 A.M. log dump would result in SQL Server error 4305 because the log had been truncated several times between 8:15 A.M. and 8:45 A.M. while the trunc. log on chkpt. option was set. Therefore, if you need to rely on your transaction log dumps for recovery, you must dump the database after turning off the trunc. log on chkpt. option or future log dumps will not be recoverable.
For databases that do not have the log on a separate device, there is no harm in having the trunc. log on chkpt. option set. If the log is located on the same device as the data, SQL Server disallows the dumping of the transaction log to a physical device—only DUMP DATABASE is allowed for backing up—so sequencing of the transaction log is not an issue.