Fix High or Low VLF Count
February 26, 2015
We started collecting a count of all VLF across the enterprise and yes there were few with high counts. I know there are no SET guidelines on how many is too low or too high. After chatting with MVP Allen Kinsel (Blog|Twitter) and Kevin Conan (Twitter) I decided to fix using this threshold. You will be able to adjust these thresholds in my script to whatever you deem reasonable.
WHERE
( (vlfcount > 50
AND tLogSizeMB <= 8 * 1024)
OR
(vlfcount > 200
AND tLogSizeMB BETWEEN 8 * 1024 + 1 AND 36 * 1024)
OR
(vlfcount > 300
AND tLogSizeMB BETWEEN 36 * 1024 + 1 AND 72 * 1024)
OR
(vlfcount > 400
AND tLogSizeMB BETWEEN 72 * 1024 + 1 AND 108 * 1024)
OR
(vlfcount > 500
AND tLogSizeMB >= 108 * 1024 + 1)
OR
( vlfCount < CEILING(tLogSizeMB / 512) )--Looking for too few VLF
) AND dbname NOT IN ( 'tempdb', 'model' )
FYI here is the algorithm for up to 2012 (Source)
Up to 64 MB: 4 new VLFs, each roughly 1/4 the size of the growth
64 MB to 1 GB: 8 new VLFs, each roughly 1/8 the size of the growth
More than 1 GB: 16 new VLFs, each roughly 1/16 the size of the growth
From 2014:
Is the growth size less than 1/8 the size of the current log size?
Yes: create 1 new VLF equal to the growth size
No: use the formula above
Once I collected this for 2000+ databases and decided to fix the bad ones I realized it will consume a lot of my time. I also realized this problem will be back even after I fix it unless I fix the initial size and Autogrowth. While doing some research I found a couple of blogs/scripts attempted to do the same. Almost all the one I looked at will shrink the file, and regrow to old size in one step. I did not like that for 2 reasons.
1. Your Autogrow is still at the old value, most likely very small which created all these VLF in the first place.
2. If you are growing your logfile in huge chunks it will create a very large size of VLF. The large size of VLF is bad because it has to finish writing the whole VLF before it can be truncated.
For all the above reason I started writing this script. This script will not change and make any modifications to anything in your environment. It will give you an output that you can copy and paste in a new query window and run to fix a high or low number of VLF. This script will only work for one log file which is recommended. Multiple log file does not give you any benefit and is not recommended. If your environment has multiple log files for one database use the script here.
You can change the initial and Autogrowth size within the script. I have commented in the appropriate section to do this. While doing my research I read these blogs and also used some of the code written by my colleague Suneel Mundlapudi who is a Senior Consultant at Fresenius Medical.
fixing-high-vlf-counts
important-change-vlf-creation-algorithm-sql-server-2014
transaction-log-vlfs-too-many-or-too-few
bug-log-file-growth-broken-for-multiples-of-4gb
This script only works with SQL2005 and beyond.
<pre class="wp-block-syntaxhighlighter-code">SET NOCOUNT ON;
USE [tempdb]
GO
--Dropping temp tables if exist
IF OBJECT_ID('tempdb..#databases') IS NOT NULL
BEGIN
DROP TABLE #databases
END
IF OBJECT_ID('tempdb..#dbccloginfo') IS NOT NULL
BEGIN
DROP TABLE #dbccloginfo
END
IF OBJECT_ID('tempdb..#dbccloginfo2012') IS NOT NULL
BEGIN
DROP TABLE #dbccloginfo2012
END
IF OBJECT_ID('tempdb..#vlfcounts') IS NOT NULL
BEGIN
DROP TABLE #vlfcounts
END
IF OBJECT_ID('tempdb..#TmpLogspace') IS NOT NULL
BEGIN
DROP TABLE #TmpLogspace
END
DECLARE @MajorVersion TINYINT
SET @MajorVersion = LEFT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(4000)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(4000)))-1)
IF @MajorVersion <= 8 -- pre-SQL2000
BEGIN
RETURN
END
--variables to hold each 'iteration'
DECLARE @query NVARCHAR(1000)
DECLARE @dbName SYSNAME
DECLARE @vlfs INT
DECLARE @logFileName NVARCHAR(200)
--table variable used to 'loop' over databases
CREATE TABLE #databases (dbname sysname)
INSERT INTO #databases
--only choose online databases
SELECT name FROM sys.databases WHERE STATE = 0
--table variable to hold results
CREATE TABLE #vlfcounts
(
dbName SYSNAME ,
vlfCount INT ,
tLogSizeMB INT ,
logFileName NVARCHAR(200)
)
--table variable to capture DBCC loginfo output
--changes in the output of DBCC loginfo FROM SQL2012 mean we have to determine the version
IF @MajorVersion < 11 -- pre-SQL2012
BEGIN
CREATE TABLE #dbccloginfo
(
fileid TINYINT ,
file_size BIGINT ,
start_offSET BIGINT ,
fseqno INT ,
[status] TINYINT ,
parity TINYINT ,
create_lsn NUMERIC(25, 0)
)
WHILE EXISTS ( SELECT TOP 1
dbname
FROM #databases )
BEGIN
SET @dbName = ( SELECT TOP 1
dbname
FROM #databases
)
SET @query = 'dbcc loginfo (' + '''' + @dbName + ''') WITH NO_INFOMSGS'
INSERT INTO #dbccloginfo
EXEC ( @query
)
SET @vlfs = @@rowcount
SET @query = 'use [' + @dbName + ']' + CHAR(10)
+ 'Select @logFileName = name FROM sys.database_files Where type =1'
EXEC sp_executesql @query, N'@logFileName varchar(200) OUTPUT',
@logFileName OUTPUT
INSERT #vlfcounts
VALUES ( @dbName, @vlfs, 0, @logFileName )
DELETE FROM #databases
WHERE dbname = @dbName
END --while
END
ELSE
BEGIN
CREATE TABLE #dbccloginfo2012
(
RecoveryUnitId INT ,
fileid TINYINT ,
file_size BIGINT ,
start_offSET BIGINT ,
fseqno INT ,
[status] TINYINT ,
parity TINYINT ,
create_lsn NUMERIC(25, 0)
)
WHILE EXISTS ( SELECT TOP 1
dbname
FROM #databases )
BEGIN
SET @dbName = ( SELECT TOP 1
dbname
FROM #databases
)
SET @query = 'dbcc loginfo (' + '''' + @dbName + ''') WITH NO_INFOMSGS '
INSERT INTO #dbccloginfo2012
EXEC ( @query
)
SET @vlfs = @@rowcount
SET @query = 'use [' + @dbName + ']' + CHAR(10)
+ 'Select @logFileName = name FROM sys.database_files Where type =1'
EXEC sp_executesql @query, N'@logFileName varchar(200) OUTPUT',
@logFileName OUTPUT
INSERT #vlfcounts
VALUES ( @dbName, @vlfs, 0, @logFileName )
DELETE FROM #databases
WHERE dbname = @dbName
END --while
END
CREATE TABLE #TmpLogspace
(
DBName NVARCHAR(150) ,
LOGSIZE_MB DECIMAL(18, 0) ,
LOGSPACE_USED DECIMAL(18, 1) ,
LOGSTATUS DECIMAL(18, 0)
)
INSERT #TmpLogspace
( DBName ,
LOGSIZE_MB ,
LOGSPACE_USED ,
LOGSTATUS
)
EXEC ( 'DBCC SQLPERF(LOGSPACE) WITH NO_INFOMSGS;'
)
ALTER TABLE #TmpLogspace
ADD SQLSrvrName NVARCHAR(100),
InstName NVARCHAR(100)
UPDATE #TmpLogspace SET SQLSrvrName = CAST(SERVERPROPERTY('MachineName') as NVARCHAR(100)) ,InstName = ISNULL(CAST(SERVERPROPERTY('InstanceName') as VARCHAR(100)),'DEFAULT')
UPDATE vlf SET tLogSizeMB = tlog.LOGSIZE_MB
FROM #vlfcounts vlf inner join #TmpLogspace tlog on vlf.dbName = tlog.DBName
--output the full list
SELECT CAST(SERVERPROPERTY('MachineName') AS VARCHAR(100)) AS SQLSrvrName ,
ISNULL(CAST(SERVERPROPERTY('InstanceName') AS VARCHAR(100)), 'DEFAULT') AS InstName ,
dbName ,
vlfCount ,
tLogSizeMB ,
logFileName
FROM #vlfcounts
WHERE ( ( vlfcount > 50
AND tLogSizeMB <= 8 * 1024
)
OR ( vlfcount > 200
AND tLogSizeMB BETWEEN 8 * 1024 + 1 AND 36 * 1024
)
OR ( vlfcount > 300
AND tLogSizeMB BETWEEN 36 * 1024 + 1 AND 72 * 1024
)
OR ( vlfcount > 400
AND tLogSizeMB BETWEEN 72 * 1024 + 1 AND 108 * 1024
)
OR ( vlfcount > 500
AND tLogSizeMB >= 108 * 1024 + 1
)
OR ( vlfCount < CEILING(tLogSizeMB / 512) )--Looking for too few VLF
)
AND dbname NOT IN ( 'tempdb', 'model' )
ORDER BY vlfCount DESC
DECLARE @databaseName NVARCHAR(200)
DECLARE @vlfCount NVARCHAR(200)
DECLARE @currentLogSize NVARCHAR(200)
DECLARE @newLogSize INT
DECLARE @physicalName NVARCHAR(200)
DECLARE @totalSize NVARCHAR(200)
DECLARE @increment INT
DECLARE @initialFileSize int
DECLARE @autoGrowth INT
DECLARE dbcrsr CURSOR
FOR
SELECT dbname ,
vlfCount ,
tLogSizeMB ,
logFileName
FROM #vlfcounts
WHERE ( ( vlfcount > 50
AND tLogSizeMB <= 8 * 1024
)
OR ( vlfcount > 200
AND tLogSizeMB BETWEEN 8 * 1024 + 1 AND 36 * 1024
)
OR ( vlfcount > 300
AND tLogSizeMB BETWEEN 36 * 1024 + 1 AND 72 * 1024
)
OR ( vlfcount > 400
AND tLogSizeMB BETWEEN 72 * 1024 + 1 AND 108 * 1024
)
OR ( vlfcount > 500
AND tLogSizeMB >= 108 * 1024 + 1
)
OR ( vlfCount < CEILING(tLogSizeMB / 512) )--Looking for too few VLF
)
AND dbname NOT IN ( 'tempdb', 'model' )
ORDER BY vlfCount DESC
OPEN dbcrsr
FETCH NEXT FROM dbcrsr INTO @databaseName,@vlfCount, @currentLogSize, @physicalName
WHILE @@fetch_status = 0
BEGIN
/*
Based on the bug mentioned by Paul Randal in his blog I am setting these depending on the sql version
<blockquote class="wp-embedded-content" data-secret="kS9BIlwVC2"><a href="https://www.sqlskills.com/blogs/paul/bug-log-file-growth-broken-for-multiples-of-4gb/">Bug: log file growth broken for multiples of 4GB</a></blockquote><iframe class="wp-embedded-content" sandbox="allow-scripts" security="restricted" style="position: absolute; clip: rect(1px, 1px, 1px, 1px);" title="“Bug: log file growth broken for multiples of 4GB” — Paul S. Randal" src="https://www.sqlskills.com/blogs/paul/bug-log-file-growth-broken-for-multiples-of-4gb/embed/#?secret=kS9BIlwVC2" data-secret="kS9BIlwVC2" width="580" height="327" frameborder="0" marginwidth="0" marginheight="0" scrolling="no"></iframe>
In this section you can change how you want to set the intial size, autogrowth and new size for log file.
*/
IF @MajorVersion < 11 --Up to SQL2008R2
BEGIN
SELECT @newLogSize = CEILING(( ( CONVERT (DECIMAL(12, 3), @currentLogSize) )
/ 1000 )) * 1000
IF ( CONVERT(INT, @newLogSize) < 4001 )--less than 4gb
BEGIN
SELECT @initialFileSize = 1000
SELECT @autoGrowth = 1000
SELECT @increment = 1000
END
ELSE
IF ( ( CONVERT(INT, @newLogSize) ) BETWEEN 4001 AND 16000 )
BEGIN
SELECT @initialFileSize = 4000
SELECT @autoGrowth = 1000
SELECT @increment = 1000
END
ELSE
IF ( CONVERT(INT, @newLogSize) > 16000 )
BEGIN
SELECT @initialFileSize = 4000
SELECT @autoGrowth = 4000
SELECT @increment = 4000
END
END
ELSE IF (@MajorVersion =11)--SQL2012
BEGIN
SELECT @newLogSize = CEILING(( ( CONVERT (DECIMAL(12, 3), @currentLogSize) )
/ 1024 )) * 1024
IF ( CONVERT(INT, @newLogSize) < 4096 )--less than 4gb
BEGIN
SELECT @initialFileSize = 1024
SELECT @autoGrowth = 1024
SELECT @increment = 1024
END
ELSE
IF ( ( CONVERT(INT, @newLogSize) ) BETWEEN 4097 AND 16384 )
BEGIN
SELECT @initialFileSize = 4096
SELECT @autoGrowth = 1024
SELECT @increment = 1024
END
ELSE
IF ( CONVERT(INT, @newLogSize) > 16384 )
BEGIN
SELECT @initialFileSize = 4096
SELECT @autoGrowth = 4096
SELECT @increment = 4096
END
END
ELSE --SQL2014
--Keeping autogrowth and increment at low size becuase of the new VLF rule in SQL2014
BEGIN
SELECT @newLogSize = CEILING(( ( CONVERT (DECIMAL(12, 3), @currentLogSize) )
/ 1024 )) * 1024
IF ( CONVERT(INT, @newLogSize) < 4096 )--less than 4gb
BEGIN
SELECT @initialFileSize = 1024
SELECT @autoGrowth = 1024
SELECT @increment = 1024
END
ELSE
IF ( ( CONVERT(INT, @newLogSize) ) BETWEEN 4097 AND 16384 )
BEGIN
SELECT @initialFileSize = 4096
SELECT @autoGrowth = 512
SELECT @increment = 512
END
ELSE
IF ( CONVERT(INT, @newLogSize) > 16384 )
BEGIN
SELECT @initialFileSize = 4096
SELECT @autoGrowth = 512
SELECT @increment = 512
END
END
PRINT '/****************************************************'
PRINT '--Fixing database : ' + @databaseName
PRINT '--Current log Size : ' + @currentLogSize
PRINT '--Current VLF Count : ' + @vlfCount
PRINT '--Log file Name : ' + @physicalname
PRINT '--Setting New Size to: ' + CONVERT (NVARCHAR(20), @newLogSize)
PRINT CHAR(10)
+ '-- Step 1: Take transaction log backup if needed, and then shrink the log file'
+ CHAR(10) + 'USE [' + @databaseName + ']' + CHAR(10) + 'GO' + CHAR(10)
+ 'DBCC SHRINKFILE (N''' + @physicalname + ''' , 0, TRUNCATEONLY);'
PRINT CHAR(10)
+ '--Step 2 : Check your vlf count now using DBCC LOGINFO, check your log file size. If size or VLF count still high take log backup, check long running transaction and shrink again.'
+ CHAR(10) + '--Once you have reasonable size and count move to step 3'
PRINT CHAR(10)
+ '--Step 3: Grow log file back to original size and adjust autogrowth so VLF count will not grow high in future.'
PRINT 'USE [MASTER];' + CHAR(10) + 'GO'
PRINT 'ALTER DATABASE [' + @databaseName + '] MODIFY FILE (NAME = N'''
+ @physicalname + ''', SIZE = ' + CONVERT (VARCHAR(20), @initialFileSize)
+ 'MB, FILEGROWTH = ' + CONVERT (VARCHAR(20), @autoGrowth) + 'MB);'
+ CHAR(10) + 'GO'
WHILE ( @initialFileSize <@newLogSize)
BEGIN
SET @initialFileSize = @initialFileSize + @increment
PRINT 'ALTER DATABASE [' + @databaseName + '] MODIFY FILE (NAME = N''' + @physicalname + ''', SIZE = ' + CONVERT (VARCHAR (20),@initialFileSize) + 'MB);' + CHAR(10) + 'GO'
END
PRINT CHAR(10)
PRINT '--Post Step: Check new VLF count'
PRINT 'USE [' + @databaseName + ']' + CHAR(10) + 'GO' + CHAR(10)
+ 'DBCC LogInfo;'
PRINT CHAR(10) + CHAR(10) + CHAR(10)
PRINT '*****************************************************/'
FETCH NEXT FROM dbcrsr INTO @databaseName,@vlfCount, @currentLogSize,@physicalName
END --dbcrsr begin
CLOSE dbcrsr
DEALLOCATE dbcrsr</pre>
9 replies on “Fix High or Low VLF Count”
I recommend adding a download link for the script. Simply highlighting and copy/paste brings over the line numbers.
Hi Derik,
Thank you for reading. Did you try the “View Source” button at the top right hand corner of code box?
This could not posbsily have been more helpful!
Hi Derik,
I did add a download link.
Thanks
Thank you for sharing this information and the script! It saved a lot of work!
The script does not work on case sensitive servers, some changes must be made:
Tempdb => tempdb
variables @logFileName, @PhysicalName are not named consistantly throughout the script
Line 172 vlf.dbname must be changed to vlf.dbName
Lines 177ff the following columen names must be changed accordingly: dbName, vlfCount, logFileName
Thank you again! 🙂
Hi Ralph,
Thank you for pointing out those issues. I will fix those.
Taiob
Hi Ralph,
Script is fixed and added a download link.
Thank you
Can we use this script on Availability Group database?
Yes on the primary.