— High VLF counts can affect write performance
— and they can make full database restores and crash recovery take much longer
— Try to keep your VLF counts under 200 in most cases (depending on log file size)

CREATE TABLE #VLFInfo
(
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
WHERE VLFCount >= 50 –over 200 VLFs is bad
ORDER BY VLFCount DESC;
DROP TABLE #VLFInfo;
DROP TABLE #VLFCountResults;