A DBA, going about his or her daily chores, ought not to be overly concerned with the internal structure of the transaction log. Nevertheless, some discussion on this topic is very helpful in understanding the appropriate log maintenance techniques, and especially in understanding the possible root cause of problems such as log file fragmentation, or a log file that is continuing to grow and grow in size, despite frequent log backups.
However, we will keep this "internals" discussion as brief as possible.
As discussed in Chapter 1, a transaction log stores a record of the operations that have been performed on the database with which it is associated. Each log record contains the details of a specific change, relating to object creation/modification (DDL) operations, as well any data modification (DML) operations. When SQL Server undergoes database recovery (for example, upon start up, or during a RESTORE operation), it, will roll back (undo) or roll forward (redo) the actions described in these log records, as necessary, in order to reconcile the data and log files, and return the database to a consistent state.
Transaction log files are sequential files; in other words SQL Server writes to the trans-action log sequentially (unlike data files, which tend to be written in a random fashion, as data is modified in random data pages). Each log record inserted into the log file is stamped with a Log Sequence Number (LSN). When a database and its associated log file are first created, the first log record marks the start of the logical file, which at this stage will coincide with the start of the physical file. The LSNs are then ever-increasing;
the most recently added log record will always have the highest LSN, and marks the end of the logical file (discussed in more detail shortly). All log records associated with a given transaction are linked in an LSN chain with forward and backward pointers to the operation in the transaction that succeeded and preceded the current operation.
Internally, SQL Server divides a transaction log file into a number of sections called virtual log files (VLFs). Figure 5-1 depicts a transaction log composed of eight VLFs, and marks the active portion of the log, known as the active log.
Chapter 5: Log Backups
Figure 5-1: A transaction log with 8 VLFs.
The concept of the active log is an important one. A VLF can either be "active," if it contains any part of what is termed the active log, or "inactive," if it doesn't. Any log record relating to an open transaction is required for possible rollback and so must be part of the active log. In addition, there are various other activities in the database, including replication, mirroring and CDC (Change Data Capture) that use the transaction log and need transaction log records to remain in the log until they have been processed.
These records will also be part of the active log.
The log record with the MinLSN, shown in Figure 5-1, is defined as the "oldest log record that is required for a successful database-wide rollback or by another activity or operation in the database." This record marks the start of the active log and is sometimes referred to as the "head" of the log. Any more recent log record, regardless of whether it is still open or required, is also part of the active log; this is an important point as it explains why it's a misconception to think of the active portion of the log as containing only records relating to uncommitted transactions. The log record with the highest LSN (i.e. the most recent record added) marks the end of the active log.
Therefore, we can see that a log record is no longer part of the active log only when each of the following three conditions below is met.
1. It relates to a transaction that is committed and so is no longer required for rollback.
2. It is no longer required by any other database process, including a transaction log backup when using FULL or BULK LOGGED recovery models.
3. It is older (i.e. has a lower LSN) than the MinLSN record.
Any VLF that contains any part of the active log is considered active and can never be truncated. For example, VLF3, in Figure 5-1, is an active VLF, even though most of the log records it contains are not part of the active log; it cannot be truncated until the head of the logs moves forward into VLF4.
The operations that will cause the head of the log to move forward vary depending on the recovery model of the database. For databases in the SIMPLE recovery model, the head of the log can move forward upon CHECKPOINT, when pages are flushed from cache to disk, after first being written to the transaction log. As a result of this operation, many log records would now satisfy the first requirement listed above, for no longer being part of the active log. We can imagine that if, as a result, the MinLSN record in Figure 5-1, and all subsequent records in VLF3, satisfied both the first and second criteria, then the head would move forward and VLF3 could now be truncated. Therefore, generally, space inside the log is made available for reuse at regular intervals.
Truncation does not reduce the size of the log file
It's worth reiterating that truncation does not affect the physical size of the log; it will still take up the same physical space on the drive. Truncation is merely the act of marking VLFs in the log file as available for reuse, in the recording of subsequent transactions.
Chapter 5: Log Backups
For databases using FULL or BULK LOGGED recovery, the head can only move forward as a result of a log backup. Any log record that has not been previously backed up is considered to be still "required" by a log backup operation, and so will never satisfy the second requirement above, and will remain part of the active log. If we imagine that the MinLSN record in Figure 5-1 is the first record added to the log after the previous log backup, then the head will remain in that position till the next log backup, at which point it can move forward (assuming the first requirement is also satisfied).
I've stressed this many times, but I'll say it once more for good measure: this is the other reason, in addition to enabling point-in-time restore, why it's so important to back up the log for any database operating in FULL (or BULK_LOGGED) recovery; if you don't, the head of the log is essentially "pinned," space will not be reused, and the log will simply grow and grow in size.
The final question to consider is what happens when the active log reaches the end of VLF8. Simplistically, it is easiest to think of space in the log file as being reused in a circular fashion. Once the logical end of the log reaches the end of a VLF, SQL Server will start to reuse the next sequential VLF that is inactive, or the next, so far unused, VLF.
In Figure 5-1, this could be VLF8, followed by VLFs 1 and 2, and so on. If no further VLFs were available at all, the log would need to auto-grow and add more VLFs. If this is not possible, due to auto-growth being disabled or the disk housing the log file being full, then the logical end of the active log will meet the physical end of the log file, the trans-action log is full, and the 9002 error will be issued.