Transaction Log Truncation

If log records were never deleted from the transaction log, it would eventually fill all the disk space that is available to the physical log files. Log truncation automatically frees disk space for reuse by the transaction log.

Except when delayed for some reason, log truncation occurs automatically as follows:

• Under the simple recovery model, after a checkpoint.
• Under the full recovery model or bulk-logged recovery model, after a log backup, if a checkpoint has occurred since the previous backup. For more information, see "Log truncation under the full and bulk-logged recovery models," later in this topic.

Although automatic, log truncation can be delayed by a variety of factors. For information about what can delay log truncation, see Factors That Can Delay Log Truncation.

For architectural information about log truncation, see "How Log Truncation Works," later in this topic.

Log truncation under the full and bulk-logged recovery models

Under the full recovery model or bulk-logged recovery model, the inactive part of the log cannot be truncated until all its log records have been captured in a log backup. This is needed to maintain the log chain—a series of log records having an unbroken sequence of log sequence numbers (LSNs). The log is truncated when you back up the transaction log, assuming the following conditions exist:

• A checkpoint has occurred since the log was last backed up. A checkpoint is essential but not sufficient for truncating the log under the full recovery model or bulk-logged recovery model. After a checkpoint, the log remains intact at least until the next transaction log backup.
  For more information, see Checkpoints and the Active Portion of the Log.

• No other factor is preventing log transaction.
  Generally, with regular backups, log space is regularly freed for future use. However, various factors, such as a long-running transaction, can temporarily prevent log truncation. For more information, see Factors That Can Delay Log Truncation.

• The BACKUP LOG statement does not specify WITH NO_TRUNCATE, WITH NO_LOG, or WITH COPY_ONLY.

  Important

  The NO_LOG and TRUNCATE_ONLY options of the BACKUP LOG statement break the log chain, because they remove the inactive part of the log without making a backup copy. Until the next full or differential database backup, the database is not protected from media failure. This feature will be removed in the next version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible.

To backup the transaction log

• How to: Back Up a Transaction Log (SQL Server Management Studio)
• How to: Create a Transaction Log Backup (Transact-SQL)

How Log Truncation Works

The transaction log is a wrap-around file. When the database is created, the logical log file begins at the start of the physical log file. New log records are added at the end of the logical log and expand toward the end of the physical log. The transaction log in a database maps over one or more physical files. The SQL Server Database Engine divides each physical log file internally into a number of virtual log files. Log truncation frees space in the logical log by deleting inactive virtual log files form the start of the logical log. For in-depth information about transaction log architecture, see Transaction Log Logical Architecture and Transaction Log Physical Architecture.

Virtual log files are the unit of space that can be reused. Only virtual log files that contain just inactive log records can be truncated. The active portion of the transaction log, the active log, cannot be truncated, because the active log is required to recover the database. The most recent checkpoint defines the active log. The log can be truncated up to that checkpoint.

When the checkpoint is performed, the inactive portion of the transaction log is marked as reusable. Thereafter, the inactive portion can be freed by log truncation. Truncation frees the inactive virtual log files for reuse. Eventually, when a new record is written to a freed virtual log, that virtual log file becomes active again.

One piece of information recorded in a checkpoint is the log sequence number (LSN) of the first log record that must be present for a successful database-wide rollback. This LSN is called the minimum recovery LSN (MinLSN). The start of the active portion of the log is the virtual log that contains the MinLSN. When a transaction log is truncated, only the log records in front of this virtual log file are freed for reuse.

The following illustrations show a transaction log before and after truncation. The first illustration shows a transaction log that has never been truncated. Currently, four virtual log files are in use by the logical log. The logical log starts at the front of the first virtual log file and ends at virtual log 4. The MinLSN record is in virtual log 3. Virtual log 1 and virtual log 2 contain only inactive log records. These records can be truncated. Virtual log 5 is still unused and is not part of the current logical log.

The second illustration shows how the log appears after being truncated. Virtual log 1 and virtual log 2 have been freed for reuse. The logical log now starts at the beginning of virtual log 3. Virtual log 5 is still unused, and it is not part of the current logical log.

See Also

Concepts

Checkpoints and the Active Portion of the Log
Setting Database Options
Working with Transaction Log Backups
Choosing the Recovery Model for a Database
Overview of the Recovery Models

Other Resources

BACKUP (Transact-SQL)
Truncate Method

Help and Information

Getting SQL Server 2005 Assistance

Change History