SQL: Virtual Log File Counts
Date: 2014-04-02 14:42:47
Virtual Log Files (VLF) are the mini logs inside the Transaction Log. Having a large number of them can translate to slow recovery times, and even in extreme cases a slow(er) system. VLF counts can grow when the transaction log isn't initially sized correctly and its allowed to auto grow in spurts.
We can use following command to check each database: DBCC LOGINFO('databasename');
If we want to check all the database, we can get creative. We'll make 2 temp Tables. Use the Stored Procedure sp_MSforeachdb to walk through each database. Execute the DBCC LogInfo command for each database. Send those results to the 1st Temp Table. Then send the ROWCOUNTS of those results to the 2nd Temp Table. The number of Rows that DBCC LogInfo outputs are how many VLFs the transaction log has. Note, if you are running SQL 2000-2008R2 comment out Line 3. SQL 2012 added a new column to the DBCC LogInfo output.
To reduce the VLF Counts: Backup your transaction log, shrink it, and manually size it to an appropriate size.
--Get the number of Virtual Log Files per Database
CREATE TABLE #LogInfo (
ResourceUnitId BIGINT, --COMMENT THIS LINE OUT IF < 2012 SQL Server
FileID INT, FileSize BIGINT, StartOffset BIGINT
, FSeqNo BIGINT, Status BIGINT, Parity BIGINT, CreateLSN NUMERIC(38))
CREATE TABLE #VLFCounts ([Database] VARCHAR(255), VLFCount INT)
EXEC sp_MSforeachdb 'Use [?];
INSERT INTO #LogInfo
EXEC(''DBCC LOGINFO([?]) WITH NO_INFOMSGS'');
INSERT INTO #VLFCounts
SELECT DB_NAME(), @@ROWCOUNT;
SELECT * FROM #VLFCounts ORDER BY VLFCount DESC;
DROP TABLE #VLFCounts;
DROP TABLE #LogInfo;