SQL Server Transaction Log Behavor. Gaps Between Active VLFs

After watching a Paul Randal’s webcast Transaction Log Performance on PASS Virtual Chapter, I was asking myself how SQL Server appends new virtual log files (VLF) and rotates when some of the VLF would be free to reuse.

Guess #1:

I have committed and uncommitted transaction which was written to transaction log in a newly created database sequentially as shown in picture 1.

Picture 1. Committed and uncommitted transactions
Picture 1. Committed and uncommitted transactions

I expect that after transaction log backup the VLFs corresponding to committed transaction will be inactive (they will have status 0 in DBCC LOGFILE).

Picture 2. Expected VLF structure
Picture 2. Expected VLF structure


I’ve inserted some data to a newly created table. The VLF structure is shown below.

VLF after transaction 1

The VLF # 1 is occupied only by the transaction 1.

I’ve started a transaction in the second connection and insert data.

VLF after transaction 2.1

The VLF #3 is occupied only by the uncommitited transaction 2.1.

In the first connection we ran transaction 3.

VLF after transaction 3

The VLF #5 is occupied only by the transaction 3.

In the second connection I ran transaction 2.2 without commit.

VLF after transaction 2.2

So eight VLFs from 1 to 8 is active. I expect that VLF occupied only by transaction 1 and 3 (i.e. VLF #1 and # 5) would become inactive after the transaction log backup. But…

VLF after log backup

Only VLF # 1 has become inactive and can be reused. VLF #5 is still active.

After I committed transaction 2 and made a transaction log backup, all VLF except #8 has become inactive.

VLF after commit and log backup


At a first glance it seems that inactive VLFs can appear only from the beginning of the transaction log file. SQL Server does not make a gap of inactive VLF between active VLFs even that VLF is occupied by the committed transaction and was actually written to log backup.

I’ve found a Sharepoint config database with gaps between active VLFs.

VLF gaps

The next interesting thing to investigate is how SQL Server fills inactive VLFs. That will be on one of my future posts.

Leave a Reply

Your email address will not be published. Required fields are marked *