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

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

Currently there is "1 comment" on this Article:

  1. […] 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 […]

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!




Monday, Oct 27 12:00PM CDT: Attend the Minion Reindex Intro Webinar.

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/