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:
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);’)
, logSizeMB – (logSizeMB * logSpaceUsedPct / 100) AS LogSpaceUnusedMB
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:
- Log Management Made Easy – This T-SQL-wrapped Powershell script turns SQLPERF/LOGSPACE into a log management super-report. Showoff.
- Why won’t the log shrink?
- Checking for Active cluster node