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
About these ads
This entry was posted in SQL Server, T-SQL. Bookmark the permalink.

2 Responses to Quick tip: Using DBCC SQLPERF(‘LogSpace’)

  1. dazpoc says:

    Excellent – far more convenient, but what does the ‘status’ column in the output mean?

    • matt.bowler says:

      Very good question, and one to which I don’t have a good answer. Neither does anyone else seem to from what I can find. BOL has this to say: “Status of the log file. Always 0.”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s