I’ve encountered a very intriguing case with transaction log files in SQL Server 2014. Maybe it is present in earlier version.
I open a database properties, click Files and then I see two transaction log files. But when I look at Windows folder I see only one. Is it funny?
Now I’ll tell you how it was happened.
I’ve created a database with a fixed size transaction log file, then add a second log file. I’ve inserted some stuff data, made some actions for the second log files would be truncated. After that I’ve removed the second log file.
As a result physical log file was really deleted, but it’s still present in SQL Server. When you look at sys.database_files you can notice that state for the second log file is different from the first.
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.
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).
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.
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.