Ghost Transaction Log File

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?

Ghost log file

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.

Ghost log file state

Script to repeat this case is ghost_transaction_log_file

Unlimited Size of the Transaction Log File. Really?

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.

Unlimited log file size

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.

Log file max size

It’s a maximum number of pages 8KB each. So it’s 2TB too.

This is the fact that described in MSDN article Maximum Capacity Specifications for SQL Server. So transaction log file can be as large as 2TB. And no more larger!

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.

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.