SQLServerPedia

I have been reviewing the number of Virtual Log Files (VLFs) in my environment and I didn't want to run DBCC LOGINFO on each individual database, so I wrote a script that would return the number of VLFs for all the databases on the server and thought I would share it.


For more information, Kimberly Tripp has a great post on VLFs including an acceptable number and how to fix them. 8 Steps to better Transaction Log throughput (look at step #8)

CREATE TABLE #LogInfo(
                FileID BIGINT,
                FileSize BIGINT,
                StartOffset BIGINT,
                FSeqNo BIGINT,
                Status BIGINT,
                Parity BIGINT,
                CreateLSN VARCHAR(50))
CREATE TABLE #LogInfo2(
                DatabaseName SYSNAME,
                FileID BIGINT,
                FileSize BIGINT,
                StartOffset BIGINT,
                FSeqNo BIGINT,
                Status BIGINT,
                Parity BIGINT,
                CreateLSN VARCHAR(50))
EXEC master.dbo.sp_MSFOREACHDB
    'USE ? INSERT INTO #LogInfo EXECUTE (''DBCC LOGINFO'');
           INSERT INTO #LogInfo2 SELECT ''?'', * FROM #LogInfo;
           DELETE FROM #LogInfo'
SELECT DatabaseName,
       COUNT(*) AS VLFs

FROM  #LogInfo2

GROUP BY DatabaseName

ORDER BY VLFs DESC

DROP TABLE #LogInfo

DROP TABLE #LogInfo2