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.

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;

About these ads
This entry was posted in SQL Server 2008 R2. Bookmark the permalink.

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

  1. Pingback: A Collection of Good Information About Controlling VLFs in SQL Server | Glenn Berry's SQL Server Performance

  2. Pingback: New Cumulative Updates for SQL Server 2008 SP1 and SP2 | Glenn Berry's SQL Server Performance

  3. Pingback: Changing your default settings for any new database is just compulsory « dbadiary

  4. Hi Glenn,
    I ran into this problem today. Database size is 8 TB and logfile size is 300 GB. I was not aware of high VLF for this database and had to restart sql server. Now after 3 hours this database is 50% recovered. I have to wait 3 more hours. My question is if I had checked the VLF before restarting the server, what could I have done to fix the issue? How can we fix the existing log file with high numbers of VLFs? In addition to your blog, I checked Michelle Ufford and Kimberly Tripp articles on the subject as well. But I would appreciate your help.
    Thanks

  5. Pingback: SQL Server Database Mirroring Tips and Tricks, Part 1 | Glenn Berry's SQL Server Performance

  6. Mark says:

    Hi Glenn,

    Does above method has a random success rate? Does it depend on which VLFs are currently being used?

    I have tried running above code and my results vary. Sometimes VLFs get cut down to a few VLFs, sometime to 178. My initial test db has 376 VLFs.

    What if I created a second LDF, sized it, then dropped the first LDF. That way nothing interferes with VLFs usage. Do you see any issues with this approach?

    Thank you!

    • Glenn Berry says:

      Mark,

      You should check the log_reuse_wait_desc to see what is keeping the VLFs from being marked as ready to be reused, like this:

      SELECT [name], log_reuse_wait_desc FROM sys.databases

      For example, if the reason is LOG_BACKUP, then you need to run a transaction log backup. You may have to run a log backup and then shrink the log file, and then repeat the process a couple of times to get the VLF count down where you expect. Then, you should manually grow the log file to a reasonable size, using a reasonable file growth increment.

      You don’t want to be adding and dropping log files here!

  7. Mark V says:

    Hi Glenn,

    Why not? I am probably missing something, but why is adding/removing LDF file is a bad idea?

    I got to do this on bunch of dbs in PROD during a maintenance window. So any approach that cuts time and risk – is a worth checking out.

    Thanks,
    Mark

    • Glenn Berry says:

      Mark,

      I am not sure what you are talking about here. Removing your only log file (.ldf file) is a very bad idea, and a very common mistake that people make when they have a “runaway” transaction log that has filled up and grown over time to a very large size. Reducing the number of VLFs within the log file is what my blog post was about, which is a completely different subject.

  8. Mark says:

    I didn’t explain it right.

    I was asking why adding second LDF, sizing it properly and then removing original(first) LDF – is a bad idea. Does this make sense?

  9. Laurence says:

    hello glenn. What about reading the trace file file log growth events? I know there is an alert that can fire when the log grows but i can’t seem to get that working correctly. It fires too often. If you can detect the log is growing — as it grows — real time then you can save it persist it historcially and then have trends and have monitors set around when it grows. This could be the starting point of managing file growth and database growth.

  10. Laurence says:

    So now i just need that alert. I was mentioning that does not seem be be woking. If firing way too much when no file growth

  11. Laurence says:

    I would be really cool if that had a growth alert for all databases. there is an instance called _total but not sure if that means all databases. What would be ideal is if the alert could pass parameters to the job its invoking but i don’t know how to do that. That way i could pass the db name into the procedure. I wasn’t sure if the event fires before its written to the trace file so that why i have logic to test for the existing of the log growth event found in the trace else i log an event of file growth. i’ll stop rambling. thanks. Laurence

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