SQL Server Performance Forum – Threads Archive

Gathering Data/Index sizes

Hi All, I am running into some confusion when trying to gather data and index size in my database. When i look in Enterprise Manager, it shows: total dbsize – 434GB
total datafile size – 404GB
datasize (grey)- 396GB
free allocated space (blue)- 10GB When i use ‘sp_spaceused’: total dbsize – 434GB
datasize – 251GB
indexsize – 150GB
reserved – 633GB
unused – 233GB Questions:
1) What is ‘reserved’ mean in sp_spaceused as my db is not 633GB? I would think that reserved and total size are the same?
2) Can i use the datasize/indexsize frolm ‘sp_spaceused’ as the most accurate measure of the total of my data and indices in my db?
3) Why, in EM, does my 2 datafiles not equal the size of my database? It is off by about 30GB? Or does it include my logfile which is 26GB? Keep in mind we have not run update usage on this since it is too large and will take too long to run (critical db). Thanks in advance.

Without update usage, you can’t know real database size information. So you can’t compare nothing actually.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Kalen’s explanation on one of the articles
Unallocated space is space in the data files that has not be allocated to any particular object (table or index) yet. It is free space, available for any object that needs more space. Space is usually allocated to objects in units of 1 extent, which are 8 contiguous pages. If an extent is allocated to a table, and you just insert one row into that table, only one page of the extent will be ‘used’, and the other 7 will be unused. Unused space belongs to a particular object but just doesn’t have anything in it. It cannot be used for new objects. Reserved is the total space that has been allocated to all objects. So reserved + unallocated = total size Within the reserved space, the unused is pages that belong to a table or index, but don’t have anything stored there yet. So data + index + unused = reserved HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>