Effect of "trunc. log on chkpt." on Transaction Logs

Two database options have an important effect 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 (which depends on the value for the recovery interval configuration option). Setting this option for a database is useful primarily in a development environment when backups of the transaction log are not wanted. However, whether this option 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, stating:

DUMP TRANsaction is not allowed while the trunc. log on chkpt. option is enabled: use DUMP DATABASE, or disable the option with sp_dboption.

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 them up), so sequencing of the transaction log is not an issue.