DBCC SQLPERF(logspace) -- Only one database, or with conditions.

Many SQL Server DBAs often use 'DBCC SQLPERF(logspace)' to analyze transaction log utilization for their databases.  It is very helpful.  When run exactly as I've quoted (but without the quotes), you will return log usage details for all databases on the server:

But - what if you have a ton of databases on the server and you don't want to be scrolling through everything to find what you care about?  Or, maybe you only wish to see those databases where the log usage is greater than X%. 

Here's a quick little trick to use DBCC SQLPERF(logspace) with conditions, only returning details for the databases you care about.  

-- optional parameters

DECLARE

       @dbname VARCHAR(155) = NULL,

       @SpaceUsed FLOAT = NULL

 

DECLARE @LOGSPACE TABLE(

       dbName VARCHAR(155),

       LogSizeMB FLOAT,

       [LogSpaceUsed%] FLOAT,

       [Status] INT

       )

INSERT @LOGSPACE

EXEC ('DBCC SQLPERF(''logspace'')')

 

-- Now pull it back for review

-- if your optional parms are null, you return log usage for all databases

SELECT dbName, LogSizeMB, [LogSpaceUsed%], [Status]

FROM @LOGSPACE

WHERE (dbName = @dbName OR @dbName IS NULL)

AND ([LogSpaceUsed%] >= @SpaceUsed OR @SpaceUsed IS NULL);

Here I have populated my optional @SpaceUsed parameter, to only return details where the log is 30% used or greater.  Most likely you'll want to use a higher @SpaceUsed, but this is just a visual so you can see what I mean.

Or in this case, I've input the @dbName:                            

Hopefully you find it useful!

More details here:

https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-sqlperf-transact-sql?view=sql-server-ver15