sp_spaceused output
-
Hello,
I have a couple of specific questions about the output of sp_spaceused and was wondering if someone could help answer them.
I ran the following on a db named MYDB:
use MYDB
sp_spaceused @updateusage = 'TRUE'
and recieved the following output:
database_name: MYDB
database_size: 902.25 MB
Unallocated space: 47.77 MB
reserved: 106992 KB
data: 71408 KB
index_size: 33792 KB
unused: 1792 KB
I realize that (unallocated space)+(reserved)+(data)+(index_size)+(unused) gives me the total size of the datafile...but I am still not clear on exactly what some of these mean...
What does "Unallocated Space" mean exactly? Why is this space not being used by data or indexes?
What does "unused" mean exactly? Why is this not included under the catagorization of "Unallocated Space"?
I've been through BOL and have read through the "space allocation" sections but am still a bit confused. I'd appreciate any feedback.
Thanks!
John
-
Hi,
I guess that Unallocated space means space in the datafile that has not been paged ready for data or that just hasnt had any data written to it yet.
I would be interested to know more.
-
Good questions to which I also would like to know the answers.
I do have some speculations about the difference between unallocated and unused space.
Speculations are:
When a database is first created you can specify a size for it.
Before an extent within the database can be used, it must be formatted. Thus database data and indexes reside with formatted extents.
Maybe, unallocated space is space which has not been assigned to an extent (and unformatted for use).
Maybe unused space is space within formatted extent(s) available for database or indexes growth.
Anyone else?
GaryA
-
database_size is the amount of physical dasd allocated to the DB.
Unallocated space is space within the above that hasn't been assigned a purpose.
Unused is space allocated to various tables, indexes (objects) ... but available.
Pages are specific to an object.
So it becomes Allocated, but you allocate 10 pages, and only put enough data in for 9.5. You would have 1/2 a page free.
Unused also comes about due to deletions, page splits, updates that shrink the size of a row ......
Better ?????
KlK -
kknudson
Better??? Yes
Do you know if unallocated space formatted or not formatted?
GaryA
-
Consider this an educated guess, "formatted".
Why, go in and allocate a 10 gig DB. Empty machine, lots of free dasd, nothing going on. IO acivity goes through the roof, and it takes forever. SQL is formating all of the pages. Even do a resore of a large DB, how long from "go" till the little bars start moving.
KlK -
I think this makes more sense now.
Thanks for your help
-
database_size: 902.25 MB
Unallocated space: 47.77 MB
reserved: 106992 KB
data: 71408 KB
index_size: 33792 KB
unused: 1792 KB
I realize that (unallocated space)+(reserved)+(data)+(index_size)+(unused) gives me the total size of the datafile
Is the above maths is correct ?
I am not able to get to this answer
database_size = reserved + Unallocated space
and not even to
database_size = (unallocated space)+(reserved)+(data)+(index_size)+(unused) .
So what is the actual maths with this numbers.
The only things which make sense is
(reserved)=(data)+(index_size)+(unused) .
Can someone explain me the how can I match the number with database_size and unallocated space and reserved
Kindest Regards,Amit Lohia
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply