Capture SQL Server VLF information using a DMV and fix it

Updated on: 07/01/2022

Introduction

While reading a summary from Brent Ozar on an SQL Server VLF problem highlighting a very well-written article on how VLF could affect SQL Server performance by David Levy, I tried to find a more modern solution other than relying on an old-school DBCC LOGINFO.

Good news, Microsoft created a sys.dm_db_log_info DMV and even provided some recommendations on what a good number of VLFs is (databases with more than 100 VLFs in the log files, which can affect the database startup, restore, and recovery time).

Problem

While DBCC LOGINFO would do the trick to capture VLF information, it’s a bit messy and sys.dm_db_log_info dynamic management view is much more pleasant to deal with (apparently, it’s only available from SQL Server 2012 2014). We will use that view to find a VLF count for every database while keeping a call to DBCC LOGINFO for the older (and unsupported) instances. [07/01/2022] We will also add 3 columns that are producing the necessary command to shrink the log and get it back to the original size.

Solution

The following solutions would iterate through all the databases (only for SQL Server 2012 and older), captures the VLF count, and report on the databases that have 1000 or more VLFs (including 3 commands to fix the high VLF count).

DECLARE @db_name nvarchar(50);
DECLARE @db_id AS SMALLINT;
DECLARE @SQLString2 AS NVARCHAR(MAX)
DECLARE @count AS INT 

DECLARE @ProductBuildMajor AS INT; SET @ProductBuildMajor = PARSENAME(CONVERT(VARCHAR,SERVERPROPERTY('ProductVersion')),4)
DECLARE @ProductBuildMinor AS INT; SET @ProductBuildMinor = PARSENAME(CONVERT(VARCHAR,SERVERPROPERTY('ProductVersion')),2)

IF OBJECT_ID('tempdb..#VLFInfo') !=0 DROP TABLE #VLFInfo; CREATE TABLE #VLFInfo (database_name VARCHAR(50), vlf_count INT, shrinkfile_command1 VARCHAR(500), shrinkfile_command2 VARCHAR(500), altertable_command VARCHAR(500))

IF (@ProductBuildMajor = 12 AND LEFT(@ProductBuildMinor,1) >= 6) OR (@ProductBuildMajor > 12) -- Applies to: SQL Server 2014 SP3 (12.0.6XXX.X) through SQL Server 2019, SQL Database.
BEGIN

	INSERT INTO #VLFInfo (database_name, vlf_count, shrinkfile_command1, shrinkfile_command2, altertable_command) 
	SELECT 	dats.name AS database_name
		, log_info.vlf_count
		, 'USE ' + QUOTENAME(dats.name) + ' DBCC SHRINKFILE (N''' + files.[name] + ''' , 0, TRUNCATEONLY)' as shrinkfile_command1
		, 'USE ' + QUOTENAME(dats.name) + ' DBCC SHRINKFILE (N''' + files.[name] + ''' , 0)' as shrinkfile_command2
		, 'ALTER DATABASE ' + dats.name + ' MODIFY FILE (NAME = N''' + files.[name] + ''', SIZE = ' + CONVERT(VARCHAR,files.size/128+1) + 'MB)' as altertable_command
	 FROM	master.sys.databases AS dats
		INNER JOIN master.sys.master_files AS files ON files.database_id = dats.database_id
		CROSS APPLY (SELECT COUNT(database_id) AS vlf_count FROM master.sys.dm_db_log_info (dats.database_id)) log_info
	WHERE	1=1
		AND dats.state_desc = 'ONLINE'
		AND dats.database_id>4 
		AND dats.name NOT IN ('rdsadmin', 'distribution')
		AND files.type_desc = 'LOG'
			
END
ELSE	-- As of 07/01/2022, you should upgrade like yesterday
BEGIN

	IF OBJECT_ID('tempdb..#log_info') !=0 DROP TABLE #log_info; CREATE TABLE #log_info
	(
	recoveryunitid tinyint,
	fileid tinyint,
	file_size bigint,
	start_offset bigint,
	FSeqNo int,
	[status] tinyint,
	parity tinyint,
	create_lsn numeric(25,0)
	)

	DECLARE db_cursor CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR
	SELECT	dats.name as database_name, dats.database_id AS database_id
	FROM	sys.databases AS dats
	WHERE	1=1
		AND dats.state_desc = 'ONLINE'
		AND dats.database_id>4  -- exclude master, msdb, model, tempdb
		AND dats.name NOT IN ('rdsadmin', 'distribution')	-- exclude aws rds, and replication

	OPEN db_cursor
	FETCH NEXT FROM db_cursor
	INTO  @db_name, @db_id

	WHILE @@FETCH_STATUS = 0
	BEGIN

		SET  @SQLString2 = N'DBCC LOGINFO (' + '''' + @db_name + ''')'PRINT @SQLString2
		INSERT INTO #log_info EXEC (@SQLString2)
		SET @count = @@rowcount
		
		
		INSERT INTO #VLFInfo (database_name, vlf_count, shrinkfile_command1, shrinkfile_command2, altertable_command) 
		SELECT	@db_name, @count
			, 'USE ' + QUOTENAME(@db_name) + ' DBCC SHRINKFILE (N''' + files.[name] + ''' , 0, TRUNCATEONLY)' as shrinkfile_command1
			, 'USE ' + QUOTENAME(@db_name) + ' DBCC SHRINKFILE (N''' + files.[name] + ''' , 0)' as shrinkfile_command2
			, 'ALTER DATABASE ' + @db_name + ' MODIFY FILE (NAME = N''' + files.[name] + ''', SIZE = ' + CONVERT(VARCHAR,files.size/128+1) + 'MB)' as altertable_command
		FROM	master.sys.master_files AS files
		WHERE	1=1
			AND database_id=@db_id
			AND files.type_desc = 'LOG'

		FETCH NEXT FROM db_cursor INTO @db_name, @db_id

	END

	CLOSE db_cursor;
	DEALLOCATE db_cursor;

END

SELECT	* 
FROM	#VLFInfo 
WHERE	1=1 
	AND vlf_count > 1000	-- based on guidance from Brent Ozar
ORDER BY vlf_count DESC

P.S. Just in case you want to see all the current DMVs in your work cube space (see Fig #1), please send an email to Scott.Brye at quest.com to request your hard copy of the super awesome SQL Server 2017 Dynamic Management Views poster from Quest Software.