SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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:


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


Matt Bowler is a DBA at Trade Me, and teaches database design and administration courses at the local institute of technology. A recent but avid discoverer of SQL Server, Matt is a regular contributor to forums at MSDN, Experts Exchange and SSC and he blogs at mattsql.wordpress.com.


Leave a comment on the original post [mattsql.wordpress.com, opens in a new window]

Loading comments...