Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Detecting and Reducing VLFs in SQL Server 2008/2008 R2 Transaction Log Files

One common, but often undetected issue that I see with SQL Server databases is a very high number of virtual log files (VLFs) inside the SQL Server transaction log file. This usually occurs because the default initial size and autogrowth increment are both far too small for most production databases. The default initial size is 3MB, with an autogrowth increment of 10 percent, which is ridiculous (see below).  You can modify those default settings for new databases by changing them in the Model system database, but I prefer to set those values explicitly when I create a new database.

image

If you have a database in Full recovery model, with almost any regular write activity, you will quickly fill up the 3MB transaction log file, causing a 10 percent autogrow to occur. Even in Simple recovery model, if you are writing to the transaction log fast enough that the Checkpoint process cannot keep up, you can easily fill up the log file and trigger that 10 percent autogrowth. In either case, you will then have a 3.3MB log file that will quickly fill up again, and grow again. Rinse and repeat, over and over. Each time the log file grows (whether it is through autogrowth or manually), you increase the number of VLFs in the log file. The number of new VLFs depends on how much the file grew by, not how big the entire transaction log is. What you want to avoid is lots of small growths. Instead, you want a small number of larger growths. Kimberly Tripp (blog | twitter) talks about how to choose an increment size for growing your log file here.

The problem with a high number of VLFs is that it can dramatically increase the amount of time it takes to restore or recover the database. Michelle Ufford (blog | twitter) relates a recent bad experience she had with this issue here. How big you should make your transaction log file depends on the amount of write activity (both for normal operation and for maintenance activities like index maintenance), and how often you do transaction log backups. How often you do transaction log backups depends on your business requirements for recovery point objective (RPO) and recovery time objective (RTO), and how large your transaction log file is (since you don’t want it to ever fill up and have to autogrow).

The script below shows some queries and commands you can use to detect and reduce the number of VLFs in your transaction log file.

-- Detecting and reducing VLFs in SQL Server 2008
-- Glenn Berry 
-- June 2010
-- http://glennberrysqlperformance.spaces.live.com/
-- Twitter: GlennAlanBerry

-- Switch to your database
USE ngmetadata;
GO

-- Check VLF Count for current database
DBCC LogInfo;

-- Check individual File Sizes and space available for current database
SELECT name AS [File Name] , physical_name AS [Physical Name], size/128.0 AS [Total Size in MB],
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS [Available Space In MB], [file_id]
FROM sys.database_files;

-- Step 1: Compressed backup of the transaction log (backup compression requires Enterprise Edition in SQL Server 2008)
BACKUP LOG [ngmetadata] TO  DISK = N'N:\SQLBackups\ngmetadataLogBackup.bak' WITH NOFORMAT, INIT,  
NAME = N'ngmetadata- Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 1;
GO

-- Step 2: Shrink the log file
DBCC SHRINKFILE (N'ngmetadata_log' , 0, TRUNCATEONLY);
GO

-- Check VLF Count for current database
DBCC LogInfo;

-- Step 3: Grow the log file back to the desired size, 
-- which depends on the amount of write activity 
-- and how often you do log backups
USE [master];
GO
ALTER DATABASE ngmetadata MODIFY FILE (NAME = N'ngmetadata_log', SIZE = 8GB);
GO

-- Switch back to your database
USE ngmetadata;
GO

-- Check VLF Count for current database after growing log file
DBCC LogInfo;

Comments

Posted by Dukagjin Maloku on 23 June 2010

As always good job from you Glenn, also in this article, thanks!

Posted by Kumar Ashish on 23 June 2010

Good article.

We are following the below mentioned rule as a best practice.

Set to auto-grow with 10 MB minimum increments (not in percentage). For large databases, this may be more than 10 MB. (Size/Growth rate < 200).

Posted by Glenn Berry on 23 June 2010

I would think about growing the log file in larger chunks than 10MB.  If you want to grow in small chunks, go for 63MB (which only creates 4 new VLFs) or 999MB (which creates 8 new VLFs). Growing 10MB creates 4 VLFs each time you do it, which would be much worse than using a larger growth size.

Posted by YSLGuru on 8 March 2011

Question.

if one has a log file with 440 VLF's (DB Recovery set to FULL) then if I wish to get the VLF count back to a more reasonable value as shown here then based on what I have read I wait till the log is backed up in full and is in a state where releasing all unused space would in effect shrink the log file down to the smallest size possible in SQL Server and then from there alter the file and add to it 8000MB.  

Is that correct?

In my situation the backups are down by the IT folks using an all-encompassing backup device and so I cannot simply do a log backup when I like else it throws off there process.  The log backup is done at specific increments and the entire DB is fully backed up once a day in the middle of the night.  If I read all of this correctly it sounds like in my case I will have to wait till the DB is backed up in full so that the log file usage is then at, effectively %0  since all data has been fully backed up and the log can then be shrunk down to the minimum allowed (1MB I think).  

Assuming this is correct and I shrunk the log file down to 1MB (this assumes no DB usage is actually occurring that would grow the log file) then at that point the DBCC LogInfo should return just a few VLFs.  Does that sound right?

Thanks

Posted by suresh0534 on 5 May 2011

Hi,

  This is really nice article, which helped me to reduce the size of the Transaction log file in Database. Thanks to Glenn Berry...

Leave a Comment

Please register or log in to leave a comment.