Transaction Logs - II

(Based on Paul S Randal articles)

          The physical architecture of the transaction log, it is split internally into smaller chunks called virtual log files (or VLFs). These are simply an aid to easier internal management of the transaction log. When a VLF becomes full, logging automatically proceeds to use the next VLF in the transaction log. You might think that eventually the transaction log will run out of space, but this is where the transaction log is so different from data files. The transaction log is really a circular file—as long as the log records at the start of the transaction log have been truncated (or cleared). Then when logging reaches the end of the transaction log, it wraps around to the start again and begins overwriting what was there before.

A log record is no longer needed in the transaction log if all of the following are true:

·         The transaction of which it is part has committed.

·         The database pages it changed have all been written to disk by a checkpoint.

·         The log record is not needed for a backup (full, differential, or log).

·         The log record is not needed for any feature that reads the log (such as database mirroring or replication).

               A log record that is still needed is called active, and a VLF that has at least one active log record is also called active. If they are all inactive, the VLF is marked as truncated. When a VLF is truncated, it is not overwritten or zeroed in any way—it is just marked as truncated and can then be reused. This process is called log truncation. Log truncation never changes the physical size of the transaction log. Active VLFs make up the logical log—the portion of the transaction log that contains all the active log records. The database itself knows where crash recovery should start reading log records within the active portion of the log—the start of the oldest active transaction in the log, the MinLSN (this is stored in the database boot page).

The check whether log truncation can take place under either of the following circumstances:

·         When a checkpoint occurs in the SIMPLE recovery model or in other recovery models when a full backup has never been taken. (This implies that a database will remain in a pseudo-SIMPLE recovery model after being switched out of SIMPLE until a full database backup occurs.)

·         When a log backup completes.

               When log truncation cannot occur, the VLFs cannot be truncated and eventually the transaction log has to grow (or another transaction log file be added). Excessive transaction log growth can cause performance problems through a phenomenon known as VLF fragmentation. Removing VLF fragmentation can sometimes lead to a dramatic improvement in the performance of log-related activities.

There are two common issues that can prevent log truncation:

·         A long-running active transaction. The entire transaction log since the first log record from the oldest active transaction can never be truncated until that transaction commits or aborts.

·         Switching to the FULL recovery model, taking a full backup, and then never taking any log backups. The entire transaction log will remain active, waiting to be backed up by a log backup.

               If the transaction log does grow to capacity and cannot grow any further, then error 9002 will be reported and you will need to take steps to provide more space, such as growing the log file, adding another log file, or removing any impediment to the log being truncated.