Truncating the Transaction Log

If log records were never deleted from the transaction log, the log would keep growing until it filled all the available space on the disks holding the log. At some point in time, old log records no longer necessary for recovering or restoring a database must be deleted to make way for new log records. The process of deleting these log records is called truncating the log.

The active portion of the transaction log can never be truncated. The active portion of the log is the part of the log needed to recover the database at any time. It must always be present in the database in case the server fails because it will be required to recover the database when the server is restarted. The record at the start of the active portion of the log is identified by the minimum recovery log sequence number (MinLSN).

The backup and restore process chosen for a database determines how much of the transaction log in front of the active portion must be retained in the database. While the log records in front of the MinLSN play no role in recovery, they are required to roll forward updates when using log backups to restore a database to the point of failure.

The log records before the MinLSN are only needed to maintain a sequence of log backups. If a log backup sequence is not being maintained, all log records before the MinLSN can be truncated at any time. If a log backup sequence is being maintained, the part of the log before the MinLSN cannot be truncated until those log records have been copied to a log backup.

If a database log backup sequence is not being maintained for a database, the database can be set into log truncate mode. The trunc. log on chkpt. database option must be set to TRUE for a database to be eligible for log truncate mode. Whether the database is actually in log truncate mode also depends on the state of the database:

• The database comes out of log truncate mode when a BACKUP DATABASE statement is executed. This is because a BACKUP DATABASE statement is the starting point of a log backup sequence.
• The database remains out of log truncate mode until an event that invalidates the log backup sequence occurs. These events are:
  • A BACKUP LOG statement is executed that references the database and specifies either the NO_LOG or TRUNCATE_ONLY options.
  • A nonlogged operation is performed in the database, such as a nonlogged bulk copy operation or a nonlogged WRITETEXT statement.
  • An ALTER DATABASE statement that adds or deletes a file in the database is executed.
• When in log truncate mode, the database remains in log truncate mode until either a DUMP DATABASE statement is executed or the trunc. log on chkpt. option is set to FALSE.

A log backup sequence cannot be maintained for a database that is in log backup mode. The only BACKUP LOG options that are valid for a database in log backup mode are the NO_LOG or TRUNCATE_ONLY options.

Note The tempdb database is always in log truncate mode. Log truncation always occurs on a checkpoint in tempdb regardless of the setting of the trunc. log on chkpt. option.

Log truncation occurs at these points:

• At the completion of a BACKUP LOG statement.
• Every time a checkpoint is processed, if the database is in truncate mode. This includes both explicit checkpoints resulting from a CHECKPOINT statement and implicit checkpoints generated by the system. For more information about the interval between automatic checkpoints, see Checkpoints and the Active Portion of the Log..

Transaction logs are divided internally into sections called virtual log files. Virtual log files are the unit of truncation. When a transaction log is truncated, all log records before the start of the virtual log file containing the MinLSN are deleted. For more information about virtual log files, see Transaction Log Physical Architecture.

The size of a transaction log is therefore controlled in one of these ways:

• When a log backup sequence is being maintained, schedule BACKUP LOG statements to occur at intervals that will keep the transaction log from growing past the desired size.
• When a log backup sequence is not being maintained, either set on the trunc. log on chkpt. database option, or schedule BACKUP LOG WITH NO_LOG or BACKUP LOG WITH TRUNCATE_ONLY statements at intervals frequent enough to keep the log from growing past the desired size.

This illustration shows a transaction log that has four virtual logs. The log has not been truncated after the database was created. The logical log starts at the beginning of the first virtual log and the part of virtual log 4 beyond the end of the logical file has never been used.

This illustration shows how the log looks after truncation. The rows before the start of the virtual log containing the MinLSN record have been truncated.

Truncation does not physically reduce the size of a log file, it simply marks virtual log files as inactive. Actually deleting portions of the log file is controlled by the DBCC SHRINKDATABASE and DBCC SHRINKFILE statements. These DBCC statements specify that the size of a log file is to be reduced when free space is available in the file. The unit of deletion is a virtual log file. Virtual logs in the active portion of the log cannot be deleted. If all the virtual logs in a log file are in the active portion of the log, the file does not shrink until a truncation marks one or more of the virtual logs as inactive.

See Also