The SQL Server Transaction Log, Part 3: The Circular Nature of the Log

In the second part of this series, I described the structural hierarchy of the transaction log. As this post is chiefly concerned with the Virtual Log Files (VLFs) I described, I recommend you read the second part before continuing.

When all is well, the transaction log will endlessly loop, reusing the existing VLFs. This behavior is what I call the circular nature of the log. Sometimes, however, something will happen to prevent this, and the transaction log grows and grows, adding more and more VLFs. In this post, I’ll explain how all this works, or sometimes doesn’t.

VLFs and Log Truncation

All VLFs have a header structure containing metadata about the VLF. One of the most important fields in the structure is the status of the VLF, and the values we’re interested in are zero, meaning the VLF is inactive, and two, meaning the VLF is active. It’s important because an inactive VLF can be reused, but an active one cannot. Note that a VLF is wholly active or wholly inactive.

A VLF will remain active while required log records are in it, so it can’t be reused and overwritten (I’ll cover log records themselves next time). Examples of reasons why log records may be required include:

• There’s a long-running transaction the log records are part of, so they cannot be released until the transaction has committed or has finished rolling back
• A log backup hasn’t yet backed up those log records
• That portion of the log has not yet been processed by the Log Reader Agent for transactional replication or Change Data Capture
• That portion of the log hasn’t yet been sent to an asynchronous database mirror or availability group replica

It’s important to note that if there are no reasons for a VLF to remain active, it won’t switch to being inactive again until a process called log truncation occurs – more on this below.

Using a simple hypothetical transaction log with only five VLFs and VLF sequence numbers starting at 1 (remember from last time that in reality, they never do), when the transaction log is created, VFL 1 is immediately marked as active, as there always has to be at least one active VLF in the transaction log—the VLF where log blocks are currently being written to. Our example scenario is shown in Figure 1 below.

Figure 1: Hypothetical, brand-new transaction log with 5 VLFs, sequence numbers 1 through 5.

As more log records are created, and more log blocks are written to the transaction log, VLF 1 fills up, so VLF 2 has to become active for more log blocks to be written to, as shown in Figure 2 below.

Figure 2: Activity moves through the transaction log.

SQL Server tracks the start of the oldest uncommitted (active) transaction, and this LSN is persisted on disk every time a checkpoint operation occurs. The LSN of the most recent log record written to the transaction log is also tracked, but it’s only tracked in memory as there’s no way to persist it disk without running into various race conditions. That doesn’t matter as it’s only used during crash recovery, and SQL Server can work out the LSN of the “end” of the transaction log during crash recovery. Checkpoints and crash recovery are topics for future posts in the series.

Eventually, VLF 2 will fill up, and VLF 3 will become active, and so on. The crux of the circular nature of the transaction log is that earlier VLFs in the transaction log become inactive so they can be reused. This is done by a process called log truncation, which is also commonly called log clearing. Unfortunately, both of these terms are terrible misnomers because nothing is actually truncated or cleared.

Log truncation is simply the process of examining all the VLFs in the transaction log and determining which active VLFs can now be marked as inactive again, as none of their contents are still required by SQL Server. When log truncation is performed, there’s no guarantee any active VLFs can be made inactive—it entirely depends on what’s happening with the database.

There are two common misconceptions about log truncation:

1. The transaction log gets smaller (the “truncation” misconception). No, it doesn’t – there’s no size change from log truncation. The only thing capable of making the transaction log smaller is an explicit DBCC SHRINKFILE.
2. The inactive VLFs are zeroed out in some way (the “clearing” misconception). No – nothing is written to the VLF when it’s made inactive except for a few fields in the VLF header.

Figure 3 below shows our transaction log where VLFs 3 and 4 are active, and log truncation was able to mark VLFs 1 and 2 inactive.

Figure 3: Log truncation marks earlier VLFs as inactive.

When log truncation occurs depends on which recovery model is in use for the database:

• Simple model: log truncation occurs when a checkpoint operation completes
• Full model or bulk-logged model: log truncation occurs when a log backup completes (as long as there isn’t a concurrent full or differential backup running, in which case log truncation is deferred until the data backup completes)

There are no exceptions to this.

Circular Nature of the Log

To avoid the transaction log having to grow, log truncation must be able to mark VLFs inactive. The first physical VLF in the log must be inactive for the transaction log to have its circular nature.

Consider Figure 4 below, which shows VLFs 4 and 5 are in use and log truncation has marked VLFs 1 through 3 as inactive. More log records are generated, more log blocks are written into VLF 5, and eventually, it fills up.

Figure 4: Activity fills up the highest physical VLF in the transaction log.

At this point, the log manager for the database looks at the status of the first physical VLF in the transaction log, which in our example is VLF 1, with sequence number 1. VLF 1 is inactive, so the transaction log can wrap around and begin filling again from the start. The log manager changes the first VLF to active and increases its sequence number to be one higher than the current highest VLF sequence number. So it becomes VLF 6, and logging continues with log block being written into that VLF. This is the circular nature of the log, as shown below in Figure 5.

Figure 5: The circular nature of the transaction log and VLF reuse.

When Thing Go Wrong

When the first physical VLF in the transaction log isn’t inactive, the transaction log cannot wrap around, so it will grow (as long as it’s configured to do so and there is sufficient disk space). This often happens because there’s something preventing log truncation from deactivating VLFs. If you find the transaction log for a database is growing, you can query SQL Server to find out if there’s a log truncation problem using this simple code below:

SELECT
      [log_reuse_wait_desc]
  FROM [master].[sys].[databases]
  WHERE [name] = N'MyDatabase';

If log truncation was able to deactivate one or more VLFs, then the result will be NOTHING. Otherwise, you’ll be given a reason why log truncation couldn’t deactivate any VLFs. There is a long list of possible reasons described here in the section Factors that can delay log truncation.

It’s important to understand the semantics of what the result is: it’s the reason log truncation couldn’t do anything the last time it tried to run. For instance, the result might be ACTIVE_BACKUP_OR_RESTORE, but you know that that long-running full backup has finished. This just means that the last time log truncation was attempted, the backup was still running.

In my experience, the most common reason for log truncation being prevented is LOG_BACKUP; i.e., go perform a log backup! But there’s also an interesting, weird behavior with LOG_BACKUP. If you continually see the result LOG_BACKUP but you know log backups are happening successfully, it’s because there is very little activity in the database and the current VLF is the same as it was the last time a log backup was performed. So, LOG_BACKUP means “go perform a log backup” or “all of the log records backed up are from the current VLF, so it couldn’t be deactivated.” When the latter happens, it can be confusing.

Circling Back…

Maintaining the circular nature of the transaction log is very important to avoid costly log growths and the need to take corrective action. Usually, this means ensuring log backups are happening regularly to facilitate log truncation and sizing the transaction log to be able to hold any large, long-running operations like index rebuilds or ETL operations without log growth occurring.

In the next part of the series, I’ll cover log records, how they work, and some interesting examples.