More details on the status of the databases, including database status, recovery model, log reuse status...

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

, [Status] 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(*), [Status]

FROM #stage

GROUP BY [Status];

TRUNCATE TABLE #stage;'

INSERT INTO #logspace

EXEC sp_executesql N'DBCC SQLPERF(logspace);'

GO

SELECT s.DatabaseName, s.[LogSize(MB)], s.[LogSpaceUsed(%)]

, CASE c.[Status] WHEN 0 THEN N'Available for overwrite/reuse' WHEN 2 THEN N' ** Active Tlog (cant be overwritten)' ELSE CAST(c.[Status] AS NVARCHAR(10)) END AS [StatusDescr]

, c.VLF_count

, d.user_access_desc

, d.state_desc

, d.recovery_model_desc

, d.log_reuse_wait_desc

FROM #logspace as

JOIN #results as [c] ON s.DatabaseName = c.DatabaseName

JOIN master.sys.databases as [d] ON c.DatabaseName = d.name

ORDER BY CAST([LogSize(MB)] AS NUMERIC(38)) DESC, c.[Status] DESC

GO