How to Fix Databases with High VLF Count
In our earlier posts on recovery, we’ve covered the importance of properly managing SQL Server Transaction Log file growth. This includes how to detect databases with a high VLF count. It can be time-consuming to manually fix high VLF count databases. To help with that pain, this post provides a script automatically reduces the number of Virtual Log Files. It’s designed to run on an as-needed-basis once you’ve identified a database with a high VLF count; typically above 2,000.
Elks have no need to fix high VLF count databases
The script shrinks the existing log file down to the smallest size possible, then grows it back to the prior size using a sensible growth increment. If the database is in simple recovery model, the script runs a checkpoint command to attempt to force transaction log truncation before growing the file. If the database is in full recovery model, one or more transaction log backups are taken to the NUL: device, in addition to the checkpoint operation, to allow the necessary transaction log truncation to occur. Ensure you take and test a full backup of all databases you run this script against, prior to running the script. For databases in full recovery model, you’ll need to take a database backup after the script runs since the prior log chain will be broken by the log backups that have been take to the NUL: device.
Ads by Google, Paying the Rent:
NOTE: Please do not run this script against databases involved in an Availability Group, Database Mirroring, Log Shipping, or Transactional Replication unless you’re prepared to deal with the potential need to recreate the secondary database(s). Shrinking a log file is not recommended unless you have a clear and present need to fix a problem, such as a very large VLF count that might negatively affect recovery.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 |
/* Summary: Reduce the number of Virtual Log Files without altering the size of the transaction log. By: Hannah Vernon Original Source: https://www.sqlserverscience.com/recovery/fix-high-vlf-count */ SET NOCOUNT ON; DECLARE @RecoveryModel nvarchar(max); DECLARE @BlockingVLFStartOffset bigint; DECLARE @ShrinkCmd nvarchar(max); DECLARE @GrowCmd nvarchar(max); DECLARE @LogFileName nvarchar(128); DECLARE @LogFileCount int; DECLARE @msg varchar(2047); DECLARE @VersionMajor int; DECLARE @VersionMinor int; DECLARE @cmd nvarchar(max); DECLARE @cmdi nvarchar(max); DECLARE @VLFCountBefore int; DECLARE @VLFCountAfter int; DECLARE @LogFileSizeMB int; DECLARE @LogFileSizeMBEnd int; DECLARE @InitialSize int; DECLARE @MaxSize int; DECLARE @Growth int; SET @VersionMajor = CONVERT(int, SERVERPROPERTY('ProductMajorVersion')); SET @VersionMinor = CONVERT(int, SERVERPROPERTY('ProductMinorVersion')); /******************************** validation checks ******************************/ IF SERVERPROPERTY('EngineEdition') < 2 OR SERVERPROPERTY('EngineEdition') > 4 /* Standard, Enterprise, Express */ BEGIN --only run on "normal" SQL Server Engines SET @msg = 'This script only runs on Express, Standard, or Enterprise Edition engines.'; RAISERROR (@msg, 14, 1); GOTO end_of_script; END --simple recovery means we don't need to take log backups, we just need to wait for the last VLF to be truncated --full (and bulk_logged) recovery means we need to take log backups until the last VLF is truncated, with a pause between SET @LogFileCount = (SELECT COUNT(1) FROM sys.database_files df WHERE df.type_desc = N'LOG'); IF @LogFileCount > 1 BEGIN SET @msg = CONVERT(nvarchar(11), @LogFileCount) + N' log files detected. This code only works with databases with a single log file.'; RAISERROR (@msg, 14, 1); GOTO end_of_script; END /****************************** initialize variables *****************************/ SET @RecoveryModel = (SELECT d.recovery_model_desc FROM sys.databases d WHERE d.database_id = DB_ID()); SET @LogFileName = (SELECT df.name FROM sys.database_files df WHERE df.type_desc = N'LOG'); SET @ShrinkCmd = N'DBCC SHRINKFILE (' + @LogFileName + N', TRUNCATEONLY) WITH NO_INFOMSGS;'; SET @LogFileSizeMB = COALESCE((SELECT CONVERT(bigint, df.size) FROM sys.database_files df WHERE df.type_desc = N'LOG'), 0) * 8192 / 1048576; IF OBJECT_ID(N'tempdb..#sizes', N'U') IS NOT NULL DROP TABLE #sizes; CREATE TABLE #sizes ( initial_log_size int NOT NULL , max_log_size int NOT NULL , growth int NOT NULL , PRIMARY KEY CLUSTERED (initial_log_size, max_log_size) ); --these values come from https://www.sqlserverscience.com/recovery/optimal-log-file-growth-and-virtual-log-files/ INSERT INTO #sizes (initial_log_size, max_log_size, growth) VALUES (16, 16, 16) , (32, 32, 32) , (32, 64, 32) , (64, 128, 64) , (64, 256, 64) , (128, 512, 128) , (128, 1024, 128) , (256, 2048, 256) , (256, 4096, 256) , (512, 8192, 512) , (512, 16384, 512) , (1024, 32768, 1024) , (1024, 65536, 1024) , (2048, 131072, 2048) , (2048, 262144, 2048) , (4096, 524288, 4096) , (4096, 1048576, 4096); ;WITH src AS ( SELECT InitialSize = s.initial_log_size , MaxSize = s.max_log_size , Growth = s.growth , PriorMax = LAG(s.max_log_size, 1) OVER (ORDER BY s.initial_log_size, s.max_log_size) FROM #sizes s ) SELECT @InitialSize = src.InitialSize , @MaxSize = src.MaxSize , @Growth = src.growth FROM src WHERE src.PriorMax <= @LogFileSizeMB AND src.MaxSize >= @LogFileSizeMB; IF OBJECT_ID(N'tempdb..##LogInfo_CD43A587', N'U') IS NOT NULL BEGIN DROP TABLE ##LogInfo_CD43A587; END CREATE TABLE ##LogInfo_CD43A587 ( DatabaseId int NULL ); IF @VersionMajor >= 11 BEGIN ALTER TABLE ##LogInfo_CD43A587 ADD RecoveryUnitId int NOT NULL --available in SQL Server 2012+; END ALTER TABLE ##LogInfo_CD43A587 ADD FileId smallint NOT NULL , FileSize float NOT NULL , StartOffset bigint NOT NULL , FSeqNo bigint NOT NULL , Status int NOT NULL , Parity tinyint NOT NULL , CreateLSN nvarchar(24) NOT NULL; CREATE CLUSTERED INDEX LogInfo_pk ON ##LogInfo_CD43A587 (FileId, FSeqNo); SET @cmd = N'DBCC LOGINFO(' + CONVERT(nvarchar(11), DB_ID()) + N') WITH NO_INFOMSGS' IF @VersionMajor >= 11 BEGIN SET @cmdi = N'TRUNCATE TABLE ##LogInfo_CD43A587; INSERT INTO ##LogInfo_CD43A587 WITH (TABLOCKX) (RecoveryUnitId, FileId, FileSize, StartOffset, FSeqNo, Status, Parity, CreateLSN) EXEC (''' + @cmd + N''');' END ELSE BEGIN SET @cmdi = N'TRUNCATE TABLE ##LogInfo_CD43A587; INSERT INTO ##LogInfo_CD43A587 WITH (TABLOCKX) (FileId, FileSize, StartOffset, FSeqNo, Status, Parity, CreateLSN) EXEC (''' + @cmd + N''');' END /****************************** the action starts here ****************************/ EXEC sys.sp_executesql @cmdi; --get VLF details from DBCC LOGINFO /* [Status] column: 0 - VLF is inactive 1 - VLF is initialized but unused 2 - VLF is active. */ SET @VLFCountBefore = (SELECT COUNT(1) FROM ##LogInfo_CD43A587 li); PRINT N'This script will shrink the log file, then grow it back to the original size with an efficient growth increment. This is done to lower VLF count.'; PRINT N'Current VLF Count is: ' + CONVERT(nvarchar(11), @VLFCountBefore); SET @BlockingVLFStartOffset = (SELECT MAX(StartOffset) FROM ##LogInfo_CD43A587 li WHERE li.Status = 2); --status "2" is "active" --Shrink the Log File to its smallest possible size EXEC (@ShrinkCmd); --get the DBCC LOGINFO details after the shrink EXEC sys.sp_executesql @cmdi; --wait until VLF truncation marks the @BLockVLFFSeqNo as unused. IF @RecoveryModel IN (N'FULL', 'BULK_LOGGED') BEGIN DECLARE @BackupCommand nvarchar(max); --pipe a log backup to NUL: and warn the user about broken log chain. SET @BackupCommand = N'CHECKPOINT; BACKUP LOG ' + QUOTENAME(DB_NAME()) + N' TO DISK = N''NUL:'' WITH NO_COMPRESSION, NO_CHECKSUM;'; EXEC (@BackupCommand); END IF @BlockingVLFStartOffset > (SELECT MIN(StartOffset) FROM ##LogInfo_CD43A587 li) BEGIN WHILE @BlockingVLFStartOffset = (SELECT MAX(StartOffset) FROM ##LogInfo_CD43A587 li WHERE li.Status = 2) --status "2" is "active" BEGIN SET @msg = CONVERT(varchar(30), GETDATE(), 120) + ': Waiting for VLF truncation.'; RAISERROR (@msg, 0, 1) WITH NOWAIT; WAITFOR DELAY '00:00:10'; --10 seconds before we check again IF @RecoveryModel IN (N'FULL', N'BULK_LOGGED') EXEC (@BackupCommand) ELSE CHECKPOINT; EXEC (@ShrinkCmd); EXEC sys.sp_executesql @cmdi; END END EXEC (@ShrinkCmd); -- shrink one more time to get the log as small as possible. EXEC sys.sp_executesql @cmdi; DECLARE @CurrentSize int; SET @CurrentSize = COALESCE((SELECT SUM(li.FileSize) FROM ##LogInfo_CD43A587 li), 0) / 1048576; WHILE @InitialSize < @CurrentSize BEGIN SET @InitialSize = @InitialSize + @Growth; END WHILE @InitialSize <= @LogFileSizeMB BEGIN SET @GrowCmd = N'ALTER DATABASE ' + QUOTENAME(DB_NAME()) + N' MODIFY FILE (NAME = N''' + @LogFileName + ''', SIZE = ' + CONVERT(nvarchar(11), @InitialSize) + N', FILEGROWTH = ' + CONVERT(nvarchar(11), @Growth) + N', MAXSIZE = ' + CONVERT(nvarchar(11), @LogFileSizeMB) + N')'; EXEC (@GrowCmd); SET @InitialSize = @InitialSize + @Growth; END EXEC sys.sp_executesql @cmdi; SET @VLFCountAfter = COALESCE((SELECT COUNT(1) FROM ##LogInfo_CD43A587), 0); SET @LogFileSizeMBEnd = COALESCE((SELECT CONVERT(bigint, df.size) FROM sys.database_files df WHERE df.type_desc = N'LOG'), 0) * 8192 / 1048576; SET @msg = 'Log has been shrunk and re-grown back to its original size. VLF Count is now: ' + CONVERT(varchar(11), @VLFCountAfter); PRINT @msg; SET @msg = 'Original size: ' + CONVERT(varchar(11), @LogFileSizeMB) + N' MB, Current Size: ' + CONVERT(varchar(11), @LogFileSizeMBEnd) + ' MB'; PRINT @msg; IF @RecoveryModel IN (N'FULL', N'BULK_LOGGED') BEGIN RAISERROR (N'THIS IS A WARNING: A fake log backup has been taken in order to reduce the size of the active portion of the transaction log. Please ensure you immediately take a full backup of the database, along with a log backup to re-establish an appropriate recovery chain.', 14, 1) WITH NOWAIT; END end_of_script: |
The output for a database in simple recovery model looks like:
This script will shrink the log file, then grow it back to the original size with an efficient growth increment. This is done to lower VLF count. Current VLF Count is: 2970 Log has been shrunk and re-grown back to its original size. VLF Count is now: 95 Original size: 29430, Current Size: 28672
For a database in full recovery model, you’ll see evidence of the required log backup actions being taken:
This script will shrink the log file, then grow it back to the original size with an efficient growth increment. This is done to lower VLF count.
Current VLF Count is: 2970
Processed 1010 pages for database 'db', file 'db_log' on file 1.
BACKUP LOG successfully processed 1010 pages in 0.023 seconds (342.815 MB/sec).
2019-04-06 15:27:38: Waiting for VLF truncation.
Processed 1 pages for database 'db', file 'db_log' on file 1.
BACKUP LOG successfully processed 1 pages in 0.004 seconds (0.854 MB/sec).
Log has been shrunk and re-grown back to its original size. VLF Count is now: 95
Original size: 29430 MB, Current Size: 28672 MB
Msg 50000, Level 14, State 1, Line 200
THIS IS A WARNING: A fake log backup has been taken in order to reduce the size of the active portion of the transaction log.
Please ensure you immediately take a full backup of the database, along with a log backup to re-establish an appropriate recovery chain.
Note the red text above is not actually an error – the text is red to ensure you pay attention to the advice to take a database backup.
Let me know if you have any questions about this post in the comments, below.
This post is part of our series of posts on recovery. Also check out our list of SQL Server Tools.
Check the Microsoft Docs for further reading about the Transaction Log, and details about Virtual Log Files.
[…] Hannah Vernon has a script to reduce the number of virtual log files you have in your transaction log: […]
Hello,
If i run this script, and the output reads “waiting for VLF truncation” , is it possible this will this run in an endless loop? I would like to implement this on a larger scale and i am concerned i will have open running transactions for long periods of time.
It is entirely possible this script will run for a long time – it essentially waits until it can shrink the log file. Any open transaction will prevent log truncation. This script itself will not block other transactions while it waits however it may block log writing while the actual shrink process is taking place. If you are concerned about a particular production critical box, you probably want to manually run the script during an off-peak time. Be aware, if you stop the script while it is waiting there should be no negative effect since the log won’t have been shrank, however if you stop it while it is growing the log back to its previous size you should be prepared to manually re-grow the log.