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 space in the logical log 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.
Important
In the event of a long delay in log truncation, the transaction log can fill up. For information about how to deal with a full transaction log, see Troubleshooting a Full Transaction Log (Error 9002).
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 COPY_ONLY.
To backup the transaction log
How Log Truncation Works
Note
Truncation does not reduce the size of a physical log file. Reducing the physical size of a log file requires shrinking the file. For information about shrinking the size of the physical log file, see Shrinking the Transaction Log.
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.
Note
For information about how virtual log files function, see Transaction Log Physical Architecture.
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.
.gif "Transaction log with four virtual logs")
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.
.gif "Log file divided into four virtual log files")