[SQL Server] Display VLF(Virtual Log File) per database

[SQL Server] Display VLF(Virtual Log File) per database

Check Log Information for a single database

DBCC LOGINFO(AdventureWorks2016)

Check Log Information for all databases

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33

CREATE TABLE #eachDB( RecoveryUnitId INT , FileID INT , FileSize BIGINT , StartOffset BIGINT , FSeqNo BIGINT , [Status] BIGINT , Parity BIGINT , CreateLSN NUMERIC(38) ); CREATE TABLE #summary( Database_Name sysname , VLF_count INT , Log_File_count INT ); EXEC sp_MSforeachdb N'Use [?]; Insert Into #eachDB Exec sp_executeSQL N''DBCC LogInfo(?)''; Insert Into #summary Select DB_Name(), Count(*), Count(Distinct FileID) From #eachDB; Truncate Table #eachDB;' -- Check Resut SELECT * FROM #summary ORDER BY VLF_count DESC; -- Drop temp tables DROP TABLE #eachDB; DROP TABLE #summary;