Fix High or Low VLF Count

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 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 adjust these threshold 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 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 couple of blogs/scripts attempted to do the same. Almost all the one I looked at shrinked 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 very large size of VLF. 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 modification to anything in your environment. It will give you an output which you can copy and paste in new query window and run to fix 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 with in 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.


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

http://www.sqlskills.com/blogs/paul/bug-log-file-growth-broken-for-multiples-of-4gb/

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

Download Script

FacebookTwitterGoogle+Share