Virtual Log Active and Inactive

Hi,
This is a script which will help to look up the active and inactive virtual log file count.


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

CREATE TABLE #summary(Database_Name sysname, VLF INT, VLF_Count INT, Log_File_ID INT)

EXEC sp_MSforeachdb N'Use [?];

Insert Into #eachDB

Exec sp_executesql N''DBCC LogInfo(?)'';

Insert Into #summary

Select DB_Name(),Status,Count( Status), FileID

From #eachDB
group by Status,FileID

Truncate Table #eachDB;'

SELECT Database_Name,Case When VLF=2 Then 'Active' else 'InActive' End as Virtual_log,VLF_Count,Log_File_ID
FROM #summary
where Database_Name not in ('model','master','tempdb','msdb')
ORDER BY Database_Name,Log_File_ID ;

DROP TABLE #eachDB;
DROP TABLE #summary;

Tushar