The effect of VLF size on shrinking the log.

3

August 26, 2013 by Kenneth Fisher

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:

VLF1

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

VLF2

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

VLF3

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

VLF4

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

VLF5

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

VLF6

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

VLF7

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.

3 thoughts on “The effect of VLF size on shrinking the log.

  1. […] log file tends to fascinate me. In fact one of my favorite posts is where I looked into the effect of VLF size on shrinking the log. So the other day I was asking about VLFs and got sent to this great video of a recorded session […]

  2. Johnson says:

    thanks great article, there i found one more article providing information about the DBCC LOG Commands. You may also check this :
    http://www.sqlmvp.org/dbcc-log-command-in-sql-server/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,148 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: