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;
Pingback: A Collection of Good Information About Controlling VLFs in SQL Server | Glenn Berry's SQL Server Performance
Pingback: New Cumulative Updates for SQL Server 2008 SP1 and SP2 | Glenn Berry's SQL Server Performance
Pingback: Changing your default settings for any new database is just compulsory « dbadiary
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
Pingback: SQL Server Database Mirroring Tips and Tricks, Part 1 | Glenn Berry's SQL Server Performance
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!
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!
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
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.
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?
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.
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
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