space used 1

[katgirl]

Hi,

I am looking for a script that I can run that will tell me, for a given database:

reserved space
space used
free space
etc

So that I can monitor database size and file space usage.

(basically what you see in 2000 taskpad view, but in report form)

THANKS MUCH

 

[mrdenny]

sp_spacedused

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[katgirl]

Wow, is it just me, or is the output from sp_spaceused really hard to decipher?

With the exception of the Database Size column, it doesn't seem to correspond at all to the data in Taskpad View. I've tried every combination of data & translog reserved, used, etc - and the numbers just don't match up.

Anyhow, I found a great script at SQL mag online that calculates all the data you see in taskpad view and stores it in a table so you can track data growth over time.

Apparently, getting this data is not as simple as it appears since it relies on an undocumented sp.

 

[mrdenny]

The meta data that sp_spaceused uses can get out of date. There is a flag that can be passed called updatestats or something (it's in BOL) that will requery the database and get the updated size.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Windy77]

Hi Katgirl,

In your last post here you say you found "a great script at SQL mag online that calculates all the data you see in taskpad view and stores it in a table so you can track data growth over time. ".

Is there any chance you would share that script ? It sounds like you were looking to log exactly the data / growth patterns I'm trying to solve (i.e. comparative TaskPad data).

Many thanks Paul M.

 

[sqlcasey]

Here you go - excellent article, worth the time investment to read through and implement, it's simple really but you must set up the jobs *exactly* as they are written

http://www.sqlmag.com/Article/ArticleID/26874/Avoiding_the_Red_Zone.html