I was wondering the other day how VLF (Virtual Log File) sizes effected shrinking the log file so I decided to do a bit of experimentation.

Note: If you aren’t familiar with VLFs you might read David Levy’s A Busy/Accidental DBA’s Guide to Managing VLFs.

First we create a new database. I’m creating the data file at 5MB since I don’t plan on putting any data into the database for these tests. I’m creating my initial log file at 64MB because this is the largest size I can create it and still get 4VLFs. You can look at Kimberly Tripp’s blog on “Transaction Log VLFs – too many or too few?” to see how many VLFs are created with a given growth of the log file along with some other good VLF info.

CREATE DATABASE [VLF_Test] ON  PRIMARY 
( NAME = N'VLF_Test', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\VLF_Test.mdf' , 
SIZE = 5MB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'VLF_Test_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\VLF_Test_log.ldf' , 
SIZE = 64MB , FILEGROWTH = 10%)
GO

So next we run DBCC LogInfo() to display the number of VLFs and their size.

DBCC LogInfo()

And get an output of:

If you look at the StartOffset of the first VLF you will note that the first page (8KB or 8192 bytes) of the log file is being used for something else. Also if you look at the VLF sizes you will notice that the last VLF is bigger. 248KB bigger to be exact. However if you add all of the VLF sizes and the extra page you come out to exactly 64MB. I’m not sure why the last VLF is larger. I would expect them all to be the same size, or possibly the first VLF being one page smaller than the rest. If anyone else knows or has a good theory I’d love to hear it.

Next I’m going to shrink the log file by 1MB then run DBCC LogInfo() again.

USE VLF_Test
GO
DBCC SHRINKFILE (N'VLF_Test_log' , 63)
GO
DBCC LogInfo()
GO

No change. I then lowered my size a bit at a time until I hit 47MB. At which point it did in fact shrink down one VLF.

USE VLF_Test
GO
DBCC SHRINKFILE (N'VLF_Test_log' , 47)
GO
DBCC LogInfo()
GO

This did lead to an interesting thought. VLFs 1-3 were less than 16MB but VLF 4 was slightly larger. The shrink worked at a reduction of 17MB but not 16. The obvious conclusion is that the shrink must be equal to or larger than the last VLF even if there are smaller VLFs in the list. Which makes a lot of sense if you think about the circular nature of the log file. IE you can’t get rid of any VLFs in the middle of the file, only off the end. I’m going to test the theory again in just a minute. First I’m going to confirm that the minimum number of VLFs is 2. Again given how VLFs work this would make sense.

So now I’m going to try to shrink down to 1MB.

USE VLF_Test
GO
DBCC SHRINKFILE (N'VLF_Test_log' , 1)
GO
DBCC LogInfo()
GO

Note we still have 2 VLFs, which seems to prove the minimum 2 VLF limit.

So now I’m going to grow the file first by 32MB to get 4 8MB VLFs then by another 64MB to get another 4 16MB VLFs.

ALTER DATABASE [VLF_Test] MODIFY FILE ( NAME = N'VLF_Test_log', 
SIZE = 65416KB )
GO
ALTER DATABASE [VLF_Test] MODIFY FILE ( NAME = N'VLF_Test_log', 
SIZE = 130952KB )
GO

USE VLF_Test
GO
DBCC LogInfo()
GO

So the first test is to shrink by ~9MB to see if one of the ~8MB VLFs will be removed. The expectation of course is that it will not. My file is now 127.88MB total so I’m going to shrink to 119MB just shy of 9MB.

USE VLF_Test
GO
DBCC SHRINKFILE (N'VLF_Test_log' , 119)
GO
DBCC LogInfo()
GO

And as expected no change. Now if we shrink down by the 16MB required get >= to the size of the last VLF we should see VLF 10 removed.

USE VLF_Test
GO
DBCC SHRINKFILE (N'VLF_Test_log' , 111)
GO
DBCC LogInfo()
GO

Yep, reacted just as expected. Now there is an interesting ramification here. If you start out with a large log file, say 16GB (which breaks down into 16 VLFs) you get 1GB VLFs. This means that you can only shrink in 1GB chunks, and the smallest your log file can only ever get to 2GB.

Filed under: Log File, Microsoft SQL Server, SQLServerPedia Syndication, VLF Tagged: Log File, microsoft sql server, transaction log, VLF