VLF truncation in SIMPLE recovery model

I recently presented a session on log file management where I discussed monitoring and managing the logical segments of the transaction log known as Virtual Log Files, or VLFs. A great question came up about VLF truncation in simple recovery model. The question posited that once a VLF contains only committed transactions, it can be truncated, even if a prior uncommitted transaction still exists in an unrelated VLF. In response to that question, I’d like to present the details about when and how VLFs are truncated, or freed-for-reuse, in simple recovery model.

A few peasants probably not discussing VLF truncation in simple recovery model.

A few peasants probably not discussing VLF truncation in simple recovery model.

If VLFs are new to you, see my post discussing the structure of the transaction log for details about the layout of the transaction log, and how VLFs work.

The [tl/dr;]: VLF truncation occurs once all log records contained in the VLF have been committed to disk, and no prior VLFs exist in the log sequence containing uncommitted transactions.

To see VLF log truncation in action, it’s easiest to create a new, dedicated database with a small log file:

Next, we’ll create a table in the database, and take an initial look at the VLFs in the transaction log:

The VLFs at this point:

╔══════════════╦══════╦══════════╦════════╗
║ begin_offset ║ size ║ sequence ║ active ║
╠══════════════╬══════╬══════════╬════════╣
║         8192 ║ 0.24 ║       37 ║      1 ║
║       262144 ║ 0.24 ║        0 ║      0 ║
║       516096 ║ 0.24 ║        0 ║      0 ║
║       770048 ║ 0.26 ║        0 ║      0 ║
╚══════════════╩══════╩══════════╩════════╝

As you can see, the first VLF is marked active, with the rest of the 4 initially-created VLFs marked inactive.

For this next bit of code, we need to open a new query window, to run the code in a separate session.

The code starts a transaction, then inserts 10 rows into our table. Now, we switch back to our original session, and take a look at the VLFs:

╔══════════════╦══════╦══════════╦════════╗
║ begin_offset ║ size ║ sequence ║ active ║
╠══════════════╬══════╬══════════╬════════╣
║         8192 ║ 0.24 ║       37 ║      1 ║
║       262144 ║ 0.24 ║       38 ║      1 ║
║       516096 ║ 0.24 ║       39 ║      1 ║
║       770048 ║ 0.26 ║       40 ║      1 ║
║      1048576 ║ 0.24 ║        0 ║      0 ║
║      1302528 ║ 0.24 ║        0 ║      0 ║
║      1556480 ║ 0.24 ║        0 ║      0 ║
║      1810432 ║ 0.27 ║        0 ║      0 ║
╚══════════════╩══════╩══════════╩════════╝

The next three VLFs are now marked active, and the log has grown to accommodate any new transactions that might occur.

Lets insert another 20 rows in the main transaction-less session, and check the log again:

As expected, more VLFs have been marked active:

╔══════════════╦══════╦══════════╦════════╗
║ begin_offset ║ size ║ sequence ║ active ║
╠══════════════╬══════╬══════════╬════════╣
║         8192 ║ 0.24 ║       37 ║      1 ║
║       262144 ║ 0.24 ║       38 ║      1 ║
║       516096 ║ 0.24 ║       39 ║      1 ║
║       770048 ║ 0.26 ║       40 ║      1 ║
║      1048576 ║ 0.24 ║       41 ║      1 ║
║      1302528 ║ 0.24 ║       42 ║      1 ║
║      1556480 ║ 0.24 ║       43 ║      1 ║
║      1810432 ║ 0.27 ║       44 ║      1 ║
║      2097152 ║ 0.24 ║       45 ║      1 ║
║      2351104 ║ 0.24 ║        0 ║      0 ║
║      2605056 ║ 0.24 ║        0 ║      0 ║
║      2859008 ║ 0.27 ║        0 ║      0 ║
╚══════════════╩══════╩══════════╩════════╝

However, since we’re in simple recovery model, and all those inserts we just did have been committed, the VLFs for those committed inserts should be available for truncation. What gives? Well, the fact that we have an uncommitted transaction in the first VLF, sequence 37, means SQL Server cannot truncate later log records. The behavior is the same, even if we issue a CHECKPOINT command, which truncates eligible VLFs:

╔══════════════╦══════╦══════════╦════════╗
║ begin_offset ║ size ║ sequence ║ active ║
╠══════════════╬══════╬══════════╬════════╣
║         8192 ║ 0.24 ║       37 ║      1 ║
║       262144 ║ 0.24 ║       38 ║      1 ║
║       516096 ║ 0.24 ║       39 ║      1 ║
║       770048 ║ 0.26 ║       40 ║      1 ║
║      1048576 ║ 0.24 ║       41 ║      1 ║
║      1302528 ║ 0.24 ║       42 ║      1 ║
║      1556480 ║ 0.24 ║       43 ║      1 ║
║      1810432 ║ 0.27 ║       44 ║      1 ║
║      2097152 ║ 0.24 ║       45 ║      1 ║
║      2351104 ║ 0.24 ║        0 ║      0 ║
║      2605056 ║ 0.24 ║        0 ║      0 ║
║      2859008 ║ 0.27 ║        0 ║      0 ║
╚══════════════╩══════╩══════════╩════════╝

Let’s rollback the uncommitted transaction, from the 2nd session:

Now, when we look at the VLFs, they’ve all been truncated except the very last VLF, which remains active and ready for new transactions:

╔══════════════╦══════╦══════════╦════════╗
║ begin_offset ║ size ║ sequence ║ active ║
╠══════════════╬══════╬══════════╬════════╣
║         8192 ║ 0.24 ║       37 ║      0 ║
║       262144 ║ 0.24 ║       38 ║      0 ║
║       516096 ║ 0.24 ║       39 ║      0 ║
║       770048 ║ 0.26 ║       40 ║      0 ║
║      1048576 ║ 0.24 ║       41 ║      0 ║
║      1302528 ║ 0.24 ║       42 ║      0 ║
║      1556480 ║ 0.24 ║       43 ║      0 ║
║      1810432 ║ 0.27 ║       44 ║      0 ║
║      2097152 ║ 0.24 ║       45 ║      1 ║
║      2351104 ║ 0.24 ║        0 ║      0 ║
║      2605056 ║ 0.24 ║        0 ║      0 ║
║      2859008 ║ 0.27 ║        0 ║      0 ║
╚══════════════╩══════╩══════════╩════════╝

Summary

Virtual Log Files cannot be re-used, even under simple recovery model, until all transactions contained in prior virtual log files have been either committed or rolled-back. This applies even if a manual CHECKPOINT command is issued. Long running transactions cause recovery duration to increase since all activity recorded in the log file, even committed transactions, must be rolled forward in any and all active VLFs.

Read our series on recovery.