vlf.gif

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.

Solution

The following solutions would iterate through all the databases, captures the VLF count and report on the databases that have 100 or more VLFs.

DECLARE @db_name nvarchar(50);
DECLARE @db_id AS SMALLINT;
DECLARE @SQLString2 AS NVARCHAR(MAX)
DECLARE @count AS INTIF OBJECT_ID(‘tempdb..#VLFInfo’) !=0 DROP TABLE #VLFInfo; CREATE TABLE #VLFInfo (ServerName VARCHAR(50), DatabaseName VARCHAR(50), VLFCount INT)

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 FOR
SELECT name as database_name, database_id AS database_id
FROM sys.databases
WHERE database_id>4  — exclude master, msdb, model, tempdbOPEN db_cursor
FETCH NEXT FROM db_cursor
INTO  @db_name, @db_id

DECLARE @ProductBuildMajor AS INT; SET @ProductBuildMajor = PARSENAME(CONVERT(VARCHAR,SERVERPROPERTY(‘ProductVersion’)),4)

DECLARE @ProductBuildMinor AS INT; SET @ProductBuildMinor = PARSENAME(CONVERT(VARCHAR,SERVERPROPERTY(‘ProductVersion’)),2)
WHILE @@FETCH_STATUS = 0
BEGIN
IF @ProductBuildMajor >= 12 AND LEFT(@ProductBuildMinor,1
) >= 6 — Applies to: SQL Server 2014 SP3 (12.0.6XXX.X) through SQL Server 2019, SQL Database.
BEGIN
SET  @SQLString2 = N’SELECT ”’ + @@SERVERNAME + ”’ AS Servername’
+ ‘,”’ + @db_name + ”’ AS database_name’
+ ‘, COUNT(database_id) AS ”vlf_count”’
+ ‘ FROM sys.dm_db_log_info(‘ + CONVERT(VARCHAR,@db_id) + ‘)’
PRINT @SQLString2
INSERT INTO #VLFInfo  ([ServerName],[DatabaseName],[VLFCount]) EXECUTE sp_executesql @SQLString2
END
ELSE
BEGIN
SET  @SQLString2 = N’DBCC LOGINFO (‘ + ”” + @db_name + ”’)’PRINT @SQLString2
INSERT INTO #log_info EXEC (@SQLString2)
SET @count = @@rowcount
TRUNCATE TABLE #log_info
INSERT INTO #VLFInfo ([ServerName],[DatabaseName],[VLFCount]) SELECT @@SERVERNAME, @db_name, @count
END
FETCH NEXT FROM db_cursor INTO @db_name, @db_id
END
CLOSE db_cursor;
DEALLOCATE db_cursor;
SELECT * FROM #VLFInfo WHERE 1=1 AND VLFCount>=100 ORDER BY VLFCount DESC
P.S. Just in case you want to see all the current DMVs in your work-cube-space, 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.
20191001_203407