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:
- Use the script to create the table and stored procedure.
- Run the SP in ReportOnly mode (default mode, @ReportOnly = 1) to see the current log space information.
- 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
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.
Good call, I think I’ll actually change the script thusly.
And…you devs and your love of OPENROWSET…*tsk*tsk*
Pingback: Dew Drop – April 1, 2014 (#1755) | Morning Dew