Check nr of virtual logfiles

As we know it is a good idéa to reduce the nr of virtuall logfiles in the transaction logg. To find out databases with many VLF you can use this small script.

CREATE TABLE #databases(
FileID INT
, FileSize BIGINT
, StartOffset BIGINT
, FSeqNo BIGINT
, [Status] BIGINT
, Parity BIGINT
, CreateLSN NUMERIC(38)
)

CREATE TABLE #total(
Database_Name sysname
, VLF_count INT
, Log_File_count INT
)

EXEC sp_MSforeachdb N'Use [?];
Insert Into #databases
Exec sp_executeSQL N''DBCC LogInfo(?)'';
Insert Into #total
Select DB_Name(), Count(*), Count(Distinct FileID)
From #databases;
Truncate Table #databases;'

SELECT *
FROM #total
ORDER BY VLF_count DESC;
DROP TABLE #databases;
DROP TABLE #total;