• If you want to see more information than just the virtual log files count, you can include DBCC SQLPERF(logspace) and include the VLF counts like this. I prefer to test for the existence of temp tables at the start of execution and drop them if necessary, rather than dropping them at the end... this leaves the temp tables available for further use within the context of my current connection, should I want to refactor my query (or whatever other reason makes sense at the time).

    IF OBJECT_ID('tempdb.dbo.#stage') IS NOT NULL

    DROP TABLE #stage ;

    IF OBJECT_ID('tempdb.dbo.#results') IS NOT NULL

    DROP TABLE #results ;

    IF OBJECT_ID('tempdb.dbo.#logspace') IS NOT NULL

    DROP TABLE #logspace ;

    GO

    CREATE TABLE #stage

    ( RecoveryUnitId INT

    , FileID INT

    , FileSize BIGINT

    , StartOffset BIGINT

    , FSeqNo BIGINT

    , [Status] INT

    , Parity INT

    , CreateLSN NUMERIC(38)

    ) ;

    GO

    CREATE TABLE #results

    ( DatabaseName SYSNAME

    , VLF_count INT

    ) ;

    GO

    CREATE TABLE #logspace

    ( DatabaseName SYSNAME

    , [LogSize(MB)] NVARCHAR(127)

    , [LogSpaceUsed(%)] NVARCHAR(127)

    , [Status] INT

    );

    GO

    EXEC sp_MSforeachdb

    N'USE [?];

    INSERT INTO #stage

    EXEC sp_executesql N''DBCC LogInfo([?])'';

    INSERT INTO #results

    SELECT DB_NAME(), COUNT(*)

    FROM #stage;

    TRUNCATE TABLE #stage;'

    INSERT INTO #logspace

    EXEC sp_executesql N'DBCC SQLPERF(logspace);'

    GO

    SELECT #logspace.*, #results.VLF_count

    FROM #logspace

    JOIN #results ON #logspace.DatabaseName = #results.DatabaseName

    ORDER BY CAST([LogSize(MB)] AS NUMERIC(38)) DESC

    GO