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.
When you create a new database in SQL Server and select Unlimited log file size, it seems that log will be unlimited, so it can grow and grow until the free space in the disk will be exhausted.
But when you look at database file properties you’ll see another value. SSMS shows 2 097 152 MB. It’s merely 2 TB.
DMV shows another value.
It’s a maximum number of pages 8KB each. So it’s 2TB too.
There’s another strange fact. According to the above mentioned article data file can be max 16Tb. At the same time SSMS shows unlimited size and sys.database_files shows -1.
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
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
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.
