Finding space used of a database

Question

Post reply

Finding space used of a database

DHeath

SSCertifiable

Points: 5198
More actions
December 12, 2007 at 12:05 pm

#121324

Thanks a ton for all reply and thoughts.. 🙂

Does anyone have a way that you can check for space used of all the databases on a server without the use of sp_spaceused stored proc. This proc has a tendency to cause quite a bit of blocking and this information is needed during production hours. Dont care so much as to the space of each table but would love to be able to use this on the databases themselves. Also there is one caveat....has to work on all versions of SQL from 7 to 2k5.

Thanks again....

-D-

DHeath

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

thecosmictrickster@gmail.com SSChampion Points: 10386 More actions · Answer 1

I have a couple of scripts I use weekly to monitor growth rates. Unfortunately, it's one script for 7/2000, one script for 2005. If you run the 2000 one on a 2005 server, you get quite different results.

You can use sp_spaceused per DB (doesn't return individual table stats)- something like:

exec sp_msforeachdb 'use ? exec sp_spaceused'

Not sure if the locking works out any differently.

I've attached the scripts I use. I think I pinched some of the code from sp_spaceused anyway, so the locking may work out the same. The output is a little more user-friendly than sp_spaceused.

Scott Duncan

MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare

DHeath SSCertifiable Points: 5198 More actions · Answer 2

DHeath

SSCertifiable

Points: 5198

December 12, 2007 at 4:33 pm

#759642

thanks a lot.. much appreciated and will try the scripts you passed along...

Thanks again

DHeath

SQL_ABD SSCertifiable Points: 5832 More actions · Answer 3

SQL_ABD

SSCertifiable

Points: 5832

December 12, 2007 at 8:25 pm

#759658

hi,

I used the attached script to check space.

Regards

Ahmed

Steve Jones - SSC Editor SSC Guru Points: 728159 More actions · Answer 4

Why do you need this during production hours? You should have enough space free in your database to last for some period of time, a month, 6, a year, something. You do not want to be running close to being out of space, especially during production hours.

Typically space used/free space is something you'd have shown once a day, calculated overnight.

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

DHeath SSCertifiable Points: 5198 More actions · Answer 5

Its ran during production hours because this system i have will monitor over 140+ sql servers and these servers are worldwide. So if there is a problem and my support team is working on any particualr system then for us to get a quick "snapshot" of the server that is current..like within the past 2 minutes then we need accurate data and it must be ran at that time. As you know servers have problems at all times of the day :). Anyways the monitoring tool runs pretty much 24/7 on some server some where... and this is not just limited to SQL...(Oracle,informix,etc) also reside out there an those alone have there own count NOT included in the 140+ and totally different scripts as well. Tried to sum it up in a brief statement.. hopefully i did..

-D-

DHeath

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

Just in case you dislike cursors as much as I... see attached...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions