GetLogSpace – table and stored procedure

We’ve talked before about how to get log space usage information in SQL Server.  After the 1,032nd time I re-wrote my custom query to also see the UNused log space, it occurs to me that this would be a nice thing to have on your server on a permanent basis.

Introducing: GetLogSpace!  It’s a simple enough thing:

  1. Use the script to create the table and stored procedure.
  2. Run the SP in ReportOnly mode (default mode, @ReportOnly = 1) to see the current log space information.
  3. Or, run it in log mode (@ReportOnly = 0) to save that data to the table. When you run the SP in log mode, it will delete data older than @retentionDays days old (default is 2 years).

The SP returns the regular SQLPERF(LOGSPACE) data, plus a simple calculation of how much log space is free. For example:

DatabaseName LogSizeMB LogSpaceUsedPct LogSpaceUNUSEDmb LogSpaceUNUSEDmb_TOTAL StatusNum Logdate
master 1082.805 1.151 12.466 61.783 0 NULL
model 0.742 42.697 0.317 61.783 0 NULL
msdb 31.68 10.072 3.191 61.783 0 NULL
tempdb 0.742 69.211 0.514 61.783 0 NULL

It’s that Log Space Unused MB column I like…this shows you at a glance how much space is free in the file.

As I note in the SP header:

You could schedule this to run every day/week/month, to track log usage over time. Or you could just run the report to see the current usage.

Note that I’m NOT advocating the shrinking of log files willy nilly.  Your logs need room to breathe, and if they regularly take up 150Gb,  they probably need 150Gb. In the cases where you’ve missed log backups  on a FULL mode database, you need to back up that log, then (MAYBE)  shrink the log file to a more reasonable size.

*** TL;DR: Don’t habitually shrink log files! ***

Remember, kids: Use and/or modify this script at your own discretion!

Download the GetLogSpace script here.

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

3 thoughts on “GetLogSpace – table and stored procedure

  1. Justin Dearing (@zippy1981)

    Cool script. However, I would default @reportOnly to 1. The default end user behavior should be to not write things to the log.

    Now I want to see if I could rewrite this thing as a UDF with openrowset.

  2. Pingback: Dew Drop – April 1, 2014 (#1755) | Morning Dew

Comments are closed.