Finding space used of a database

Question

Finding space used of a database

DHeath

SSCertifiable

Points: 5200
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: 5200 More actions · Answer 2

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

hi,

I used the attached script to check space.

Regards

Ahmed

Steve Jones - SSC Editor SSC Guru Points: 736291 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.

DHeath SSCertifiable Points: 5200 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: 1004378 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)

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 7

How about 'setting execution context' instead of a 'use' statement:

exec sp_msforeachdb 'exec ?..sp_spaceused'

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 8

This script gives you a number of ways to analyze space usage on a server:

File Details

Total by Database and File

Total by Database and Filegroup

Total by Database and Filegroup Type

Total by Disk, Database, and Filepath

Total by Disk and Database

Total by Database

This script works for SQL 7, 2000, and 2005.

Get Server Database File Information

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89058

DHeath SSCertifiable Points: 5200 More actions · Answer 9

Thanks everyone.. much appreciated...

-D-

ssc comes thru again...as usual 😎

DHeath

SQL_ABD SSCertifiable Points: 5832 More actions · Answer 10

Hi guys,

How to find out databases used as mirror (all time in restoring mode),

to be excluded from the scripts.

Thanks,

Ahmed

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

You could add some logic utilising IF DATABASEPROPERTYEX([dbname], 'Status') = 'ONLINE'...

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