Quick tip: Using DBCC SQLPERF(‘LogSpace’)

A lot of a DBA’s time is spent managing and investigating transaction log files.

A commonly used tool is the command:

DBCC SQLPERF('LogSpace')

which returns a list of all databases, the size of their transaction log file, and the percentage of that space that is used for active transactions. One limitation of the command is that the results are returned in no particular order – on an instance with a large number of databases this can be difficult to read.

So I wrote a quick helper script to load the data into a temporary table allowing me to use a where clause to filter the results.

It’s not rocket science – but it is handy:

declare @LogSpace table
(
DatabaseName varchar(255),
[Log Size (MB)] float,
[Log Space Used (%)] float,
[Status] int)
insert into @LogSpace
execute('dbcc sqlperf(''LogSpace'')')

select * from @LogSpace
--where DatabaseName = '' --use for a particular database
--order by [Log Size (MB)] desc --find the biggest log file
--order by [Log Space Used (%)] desc --find the fullest log file