SQL Tip: Get Log Space with DBCC SQLPERF(LOGSPACE)

Every so often I’m reminded that the “basics” aren’t really basics. None of us go to take “How to be a Microsoft SQL Server DBA 101″ before our first gig. So for your perusal, use and enjoyment: here is the easiest way to get a report on log space usage:

DBCC SQLPERF(LOGSPACE);

Go ahead, type it in SSMS and hit ctrl-E. It’s gorgeous, isn’t it? You can just look at the results, or feed them into a temp table, or paste them into Excel, or whatever you want. Just get that basic command down, type it a few times a day… DBCC SQLPERF(LOGSPACE); DBCC SQLPERF(LOGSPACE);

It’s fun! You know, if “fun” means “useful data with few keystrokes”.

Example: Log Space into a Temporary Table

Here’s an example of loading the LOGSPACE data into a table, and getting a useful result:

CREATE TABLE #logspace
( [dbname] sysname
, logSizeMB float
, logSpaceUsedPct float
, Status int);

INSERT INTO #logspace
EXEC (‘DBCC SQLPERF(LOGSPACE);’)

SELECT dbname
, logSizeMB
, logSpaceUsedPct
, logSizeMB – (logSizeMB * logSpaceUsedPct / 100) AS LogSpaceUnusedMB
FROM #logspace
ORDER BY LogSpaceUnusedMB DESC;   
— Order by the biggest “wastes” of log space, on downward…

Here’s the Books Online page for DBCC SQLPERF, FYI. And a couple of (at least tangentially) related blogs by Sean:

Happy days,
Jen McCown
http://www.MidnightDBA.com/Jen

One thought on “SQL Tip: Get Log Space with DBCC SQLPERF(LOGSPACE)”

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>