SQL Server – Get VLF Counts for All Databases in an Instance

Below is a script to get the VLF count for all databases on an instance.

CREATE TABLE #VLFInfo
(
    RecoveryUnitID INT
    ,FileID INT
    ,FileSize BIGINT
    ,StartOffset BIGINT
    ,FSeqNo BIGINT
    ,Status BIGINT
    ,Parity BIGINT
    ,CreateLSN NUMERIC(38)
);

CREATE TABLE #VLFCountResults
(
    DatabaseName sysname
    ,VLFCount INT
)
    
EXEC sp_MSforeachdb N'Use [?];

        INSERT INTO #VLFInfo
        EXEC sp_executesql N''DBCC LOGINFO([?])'';

        INSERT INTO #VLFCountResults
        SELECT DB_NAME(), COUNT(*)
        FROM #VLFInfo;

        TRUNCATE TABLE #VLFInfo;'

SELECT 
    DatabaseName
    ,VLFCount
FROM 
    #VLFCountResults
ORDER BY 
    VLFCount DESC

DROP TABLE #VLFInfo
DROP TABLE #VLFCountResults

-- High VLF counts can affect write performance
-- and they can make database restored and recovery take much longer

    

Knowledge worth sharing...

Linkedin

Facebook

Google

Twitter