If the trunc. log on chkpt. option is set for a database, when SQL Server issues a checkpoint in that database, the transaction log is truncated up to the page containing the oldest outstanding transaction. A transaction is considered outstanding if it has not been committed or rolled back. If replication is being used, a transaction is also considered outstanding if it has not yet been replicated.
Important Only implicit checkpoints performed automatically by SQL Server result in this truncation. A CHECKPOINT statement issued by a user does not cause truncation of the transaction log, even when the trunc. log on chkpt. option is set.
An automatic checkpoint process wakes up approximately every 60 seconds and cycles through every database to determine if a checkpoint is needed. This determination is based on the recovery interval configuration option and the number of rows added to the log since the last checkpoint. Only those rows associated with committed transactions are considered in the calculation.
If the trunc. log on chkpt. option is set, the checkpoint process attempts to truncate the log every 60 seconds, regardless of the recovery interval or the number of log records. If there are no committed transactions in the log, the log is not truncated.
The trunc. log on chkpt. option, which is set using the sp_dboption system procedure, is not recommended in production environments where transaction logs are needed for recovery from media failure because it does not save the information contained in the log.
The transaction log of the tempdb database is automatically truncated during every cycle of the checkpoint process, about every 60 seconds. This occurs whether or not the trunc. log on chkpt. option is set on tempdb.