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.
I expect that after transaction log backup the VLFs corresponding to committed transaction will be inactive (they will have status 0 in DBCC LOGFILE).
Test
I’ve inserted some data to a newly created table. The VLF structure is shown below.
The VLF # 1 is occupied only by the transaction 1.
I’ve started a transaction in the second connection and insert data.
The VLF #3 is occupied only by the uncommitited transaction 2.1.
In the first connection we ran transaction 3.
The VLF #5 is occupied only by the transaction 3.
In the second connection I ran transaction 2.2 without commit.
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…
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.
Conclusion:
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.
The next interesting thing to investigate is how SQL Server fills inactive VLFs. That will be on one of my future posts.