Introduction
While reading a summary from Brent Ozar on a 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.
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. 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 INT
IF 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, tempdb
OPEN db_cursor
FETCH NEXT FROM db_cursor
INTO @DB_NAME, @DB_ID
DECLARE @ProductBuild AS INT; SET @ProductBuild = PARSENAME(CONVERT(VARCHAR,SERVERPROPERTY(‘ProductVersion’)),4)
WHILE @@FETCH_STATUS = 0
BEGIN
IF @ProductBuild >= 11 — Applies TO: SQL Server 2014 (12.x) through SQL Server 2017, 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 DESCP.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.
