Home » Admin »sql server »sqlserverpedia-syndication »SSC »Tech and Learning » Currently Reading:

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

Currently there are "3 comments" on this Article:

  1. 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. […] GetLogSpace – table and stored procedure (Jen McCown) […]

Comment on this Article:







Minion Reindex by MidnightDBA is here!

 

Excellent Index Maintenance

Download Minion Reindex, log feature requests, read documentation, and sign up for the newsletter at MidnightSQL.com/Minion!


 

MidnightSQL Consulting

Need help? Got an emergency? Write us at Support@MidnightDBA.com!

We can schedule time to help with your backup/restore issues, high availability and disaster recovery setup, performance problems, and a great deal more. Very often, we're even available on the moment for downtime issues and emergencies.

For more information about MidnightSQL consulting, email us or check out www.MidnightSQL.com. Happy days!

Where are We?

November 3-7: PASS Summit, Seattle, WA

PASS Summit: Jen is presenting How to Interview a DBA: A Panel Debate on Thursday 11/6 1:30pm, room 401 (along with Adam Machanic, Sean McCown, Bob Pusateri, and Michelle Ufford).

PASS Summit: Sean is presenting Performance Tuning Your Backups on Wednesday 11/5 3:00pm, room 602-604.

December 11: Presenting "Powershell Cmdlets.." at Alaska SQL Server User Group

January 30: "Become an Enterprise DBA" precon at Austin SQL Saturday

Blog Posts by Category

DBAs@Midnight

How to Eat Pop-tarts
Watch DBAs@Midnight live on Fridays,m 11pm Central time

The best database career advice you’ve never heard!

DBARoadmap.com

The DBA Roadmap Seminar is 7 MP3 tracks (over 5 hours!) of insider guidance on your database career. We'll teach you how and what to study as a DBA, weigh in on controversial resume debates, teach you to recognize a worthy recruiter, and discuss the new professionalism of interviews. Also some bonus materials, PDF companion guides, and really spiffy intro music!

Once your $99 PayPal payment is submitted, you'll get the download link in e-mail! (Download is a 370Mb ZIP file.)

Become a DBA. Become a BETTER DBA. Use the Roadmap.

Visit www.DBARoadmap.com for info, forums, and more!

Add to Cart View Cart

Cunningham’s Law

"The best way to get the right answer on the Internet is not to ask a question, it's to post the wrong answer."
Relevant: http://xkcd.com/386/