sp_spaceused output

[icemel]

Hi,

When you run sp_spaceused on a database, you get values for these items:

1.) reserved
2.) Data
3.) index_size
4.) Unused

I'm not sure what "reserved" means (BOL is not very descriptive)... or what it's used for??

And what is the significance of "unused" - does this indicate the amount by which the database can be shrunk?

My assumption is - if it's NOT unused, it can't be shrunk down? Or is this wrong?

THANKS

 

[SQLBill]

My understanding is that 'unused' space is just like free space on the hard drive, with the exception that this free space is 'held' by SQL Server. So, yes you should be able to shrink the database that amount. However, do you really want to? It's already held by SQL Server. If you return it to the operating system and then SQL Serve needs more room, it has to go back and grab space from the OS before it can use it. This CAN cause your transaction to time out if it isn't done quickly enough.

-SQLBill

Posting advice: FAQ481-4875

 

[mrdenny]

I would agree with SQLBill's description of those values.

Denny
MCSA (2003) / MCDBA (SQL 2000)

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

http://www.mrdenny.com

 

[icemel]

So, what is "reserved" and what is it used for? thanks

 

[mrdenny]

Reserved is how much space is allocated to the object (be it a table or a database).

When looking at a database this includes all the data, indexes, free space within the database, lost space within the pages, etc.

Denny
MCSA (2003) / MCDBA (SQL 2000)

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

http://www.mrdenny.com