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.
I’ve encountered a strange situation when I could not find SQLServer:Databases group of counters in Windows Performance Monitor.
It was happened on my test system with SQL Server 2014. I checked it on production SQL Server 2008 R2 – everything is OK.
The most important thing to solve this problem was found in Windows Application Log. It was error 8310.
Simple search in Google with the error message leads me to the description of that particular problem. It’s on SQL Server 2014 installation and is corrected by installing Cumulative Update 2. I’ve installed CU7 and now it works.
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.