There have been numerous articles written about the problem of too many or too few VLFs in your transaction log. So many that I’m not going to rehash the problem in this post. Go read these links if you haven’t heard of this issue.
The VLF Count/Growth Problem
Resizing the log to contain the “right” number of VLFs isn’t hard but it does require a bit of math. The problem is that the number of VLFs created when you grow a log file isn’t linear. Any growth between 1MB and 64MB will contain 4 VLFs. All growths between 64 and 1GB will contain 8VLFs. And a growths greater than 1GB will contain 16 VLFs. Imagine you want your log file to be 5000MB. If you direct a single growth of 5000MB you will get 16VLFs. If you grow it at 100, 50MB growths you get 400VLFs. It is necessary to do intermediate growths to get the file size you want and the number of VLFs you want. The difficulty is that intermediate growths can give 4, 8 or 16 VLFs per growth depending on the size of each intermediate growth.
The logic to do this isn’t hard but it is iterative and a bit time consuming. I was faced with having to resize several hundred log files and decided that I didn’t want to do the iterative logic required for each one. So, I wrote a script to do it for me.
Choosing How to Grow to Get The VLFs You Want
Categorically we can think of there being three different growth algorithm and we need to figure out which one to use for our given log size and VLF requirements. So the script needs a little logic to figure out the number of growths and the size of each. This little example explains the logic of the script when we want a 1500MB log file with 80 VLFs.
If I want 80 VLFs I can divide this by the number of VLFs created with each of the different growth algorithms.
80 /4 = 20 file growths
80/ 8 = 10 file growths
80/ 16 = 5 file growths
Take the total size and divide by the number of growths required for each growth algorithm to get the number of MB per growth.
1500/20 = 75 MB/growth (for 20 file growths)
1500/10 = 150 MB/growth (for 10 file growths)
1500/5 = 300 MB/growth (for 5 file growths)
Now we can simply pick the growth algorithm which lines up with reality.
Twenty file growths at 75MB each would create 160 VLFs.
Ten growths at 150MB would create 80VLFs.
Five growths at 300MB would create 40 VLFs.
So we need to do ten growths at 150MB each to get the requisite 80 VLFs.
The link to Brent Ozar Unlimited recommends shrinking the log file and regrowing it. When you shrink the log you will still be left with a few small VLFs. Those first few VLFs will never go away. When you use the script to grow the log file you will always have a few more than the number you specific. This isn’t a problem. The best number of VLFs isn’t an exact science; you just want to be in the ball park of what is reasonable. The script doesn’t shrink the log first though. You have to do that on your own.
I hope you find this script useful. I tested it on SQL 2012 and have used it on SQL2008R2 with excellent results. YMMV.
Double click anywhere on the script to select the entire thing so you can copy it to your clipboard
-- growing the log file -- <= 64MB -4 VLFs -- = 64MB to <= 1024MB - 8 VLFs -- > 1024 MB - 16 VLFs DECLARE @VLFs int --use a value divisible by 16 DECLARE @LogGrowSize float --in MB DECLARE @MBVLF float DECLARE @GrowthFactor tinyint DECLARE @Grow4Size float DECLARE @Grow8Size float DECLARE @Grow16Size float DECLARE @Grow4Growths int DECLARE @Grow8Growths int DECLARE @Grow16Growths int DECLARE @GrowSizeToUse int DECLARE @NumberOfGrowths int DECLARE @DBName sysname DECLARE @LogFileName sysname DECLARE @CurrentSizeMB bigint DECLARE @NewSize bigint DECLARE @Exec bit DECLARE @Msg varchar(50) --edit these control variables to your liking --don't forget to change @Exec to 1 to --actually cause the file growth to occur. SET @VLFs = 96 --the number of VLFs. Use a number divisible by 16 SET @LogGrowSize = 1000 --Size in MB to grow SET @DBName = 'TestDB' --The database whose log you want to grow SET @Exec = 0 --set to 0 to see what the growths will be. --growths below 1MB create 1, 2 or 3 VLFs depending on the size. --I didn't handle these conditions IF @LogGrowSize < 1 BEGIN RAISERROR('This script does not work for growths less than 1MB',16,1) GOTO TheEnd END --make sure there is only one log file IF (SELECT COUNT(*) FROM sys.master_files WHERE database_id = DB_ID(@DBName) and type = 1) > 1 BEGIN RAISERROR('This script works on databases with only one log file',16,1) GOTO TheEnd END --Get the logical name of the log file to use later SELECT @LogFileName = name FROM sys.master_files WHERE database_id = DB_ID(@DBName) and type = 1 IF @VLFs % 16 <> 0 BEGIN RAISERROR('Desired VLF count must be divisible by 4, 8 and 16',16,1) GOTO TheEnd END --Get the number of growths needed for each growth --pattern (4, 8 or 16 created for each growth) SET @Grow4Growths = @VLFs/4 SET @Grow8Growths = @VLFs/8 SET @Grow16Growths = @VLFs/16 --Now get the number of growths required for each algorithm SET @Grow4Size = @LogGrowSize/CAST(@Grow4Growths as float) SET @Grow8Size = @LogGrowSize/CAST(@Grow8Growths as float) SET @Grow16Size = @LogGrowSize/CAST(@Grow16Growths as float) --decide which growth pattern will get us the desired log size and VLFs. IF @Grow4Size <= 64.0 BEGIN --can't use decimals in the SIZE clause of ALTER DATABASE and --since it breaks on =64 CEILING doesn't risk putting us --into a higher VLF growth rate SET @GrowSizeToUse = CEILING(@Grow4Size) SET @NumberOfGrowths = @Grow4Growths SET @GrowthFactor = 4 END IF @Grow8Size > 64 AND @Grow8Size <= 1024 BEGIN --can't use decimals in the SIZE clause of ALTER DATABASE and since --it breaks on =1024 CEILING doesn't risk putting us into a higher --VLF growth rate. If we had a value of 64.1 there is the danger of --rounding down to 64 which would drop us into a --lower VLF growth rate so using CEILING instead of ROUND. SET @GrowSizeToUse = CEILING(@Grow8Size) SET @NumberOfGrowths = @Grow8Growths SET @GrowthFactor = 8 END IF @Grow16Size > 1024 BEGIN --See above notes about the use of CEILING SET @GrowSizeToUse = CEILING(@Grow16Size) SET @NumberOfGrowths = @Grow16Growths SET @GrowthFactor = 16 END --see what the growth size and number of growths will be for each algorithm SELECT @Grow4Size [Grow4Size] ,@Grow4Growths [Grow4Growths], @Grow8Size [Grow8Size], @Grow8Growths [Grow8Growths], @Grow16Size [Grow16Size], @Grow16Growths [Grow16Growths] --The parameters the scipt has chosen to use SELECT @GrowthFactor [GrowthFactorToUse], @GrowSizeToUse [GrowSizeToUse_MB], @NumberOfGrowths [NumGrowths], @VLFs [VLFs], CAST(@GrowSizeToUse as float)/@GrowthFactor [VLFSize_MB] --grow the log in the necessary increments DECLARE @i int = 0 DECLARE @stmt varchar(max) WHILE @i < @NumberOfGrowths BEGIN --We have to take the current size and add the growth size to get a new --size to pass into ALTER DATABASE. The current size may not be --divisible on 1MB boundaries. Ex. A size of 150 pages would be 1.17MB. --We need to round up or down to a MB boundary so the current size can --be added to the growth size to get the new size. I chose to round down. --The conversion of this to a bigint should round this down to the --lowest MB boundary. --Regardless of rounding up or down because of this the VLFs in the first --growth may not be equal size to the other new VLFs. --It should, however, be close enough. This doesn't have to be --an exact science. SELECT @CurrentSizeMB = (size * 8/1024) FROM sys.master_files WHERE database_id = DB_ID(@DBName) and type = 1 SET @NewSize = @CurrentSizeMB + @GrowSizeToUse SET @stmt = N'ALTER DATABASE ' + @DBName + N' MODIFY FILE (NAME = ''' + @LogFileName + N''', SIZE = ' + CAST(@NewSize AS nvarchar(20)) + N' MB)' IF (@Exec = 1) EXEC(@stmt) SET @i = @i + 1 SET @Msg = 'Log growth ' + cast(@i as varchar(10)) + ' is complete.' RAISERROR (@Msg, 0, 1) WITH NOWAIT END IF @Exec = 1 BEGIN SELECT size [size_in_pages],size * 8/1024 [size_in_mb] ,* FROM sys.master_files WHERE database_id = DB_ID(@DBName) and type = 1 DBCC LOGINFO END --where we end up if there is an user defined error TheEnd: