I noticed the SSMS built in disk usage reports were reporting unusually low values for unused space. sp_spaceused reports the same. I did a brief experiment:
I ran a maintenance plan to rebuild indexes with the option to use 10% free space. sp_spaceused reports:
size = 24 GB, unallocated = 5 GB, reserved = 19 GB, data = 10 GB, index size = 9 GB, unused = 0 (values are rounded).
Then the same thing with 50% free space and sp_spaceused reports:
size = 37 GB, unallocated = 4 GB, reserved = 34 GB, data = 18 GB, index size = 16 GB, unused = 0
So the question is, shouldn't unused be showing a big number in the second case to reflect the 50% free space on indexes?
If not, how would one tell that there is actually a bunch of free space?
Sql Server 2005 64-bit standard edition on Server 2008, up to date service packs. I get the same results on 3 identical servers, and multiple databases so hopefully I'm just lacking some knowledge.
Thanks
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Sorry, let me try again.
The database is originally 24 GB. I rebuild the indexes specifying to leave 50% free space instead of 10%and this causes the database to grow to 37 GB. So there would seem to be an additional 13 GB of empty space. I thought the "unused" output on sp_spaceused would show me this empty space but it doesn't.
Real world, someone shows me a big database and wonders why it is so big. How would I recognize that it had indexes built with a high amount of free space and tell them that simply rebuilding the indexes with less free space would make the database much smaller?
This is due to the fact that the unused space is space that is NOT reserved or allocated to database objects. The indexes that are at 50% space used on each page, still allocate the pages to that object, so it is literally allocated but may not contain data. Unused space is that space that does not have any object's data on it.
so you could look at sp_spaceused and see the reserved and the data size, but the main thing you would have to do is to use the dynamic management views like sys.dm_db_index_physical_s
Yes, but...
You're saying this extra 50% is allocated to the indexes and doesn't have anything in it, but doesn't that mean it should be showing up as a big number for "unused" from sp_spaceused? The documentation for sp_spaceused unused says:
Total amount of space reserved for objects in the database, but not yet used
but I unused remains small in my test. And I see what you're saying about the dynamic management view but it seems like a lot of work to go from that to get my answer.
So the 2 numbers that should actually make sense in the output are these:
unallocated = the space that is in the file that is not assigned to any object (you have a 20 GB file and 18 GB is allocated in pages to objects, so 2 GB is Unallocated and would show in this column)
unused = the space that has been reserved by objects but does not have data in it. This includes indexes and data pages.
It should reconcile, and the way you may be able to tell is to use an object on the sp_spaceused.
sp_spaceused 'tablename'
this would should you the rowcount, reserved amount of space, data and index usage and what was unused in the table or index pages.
I am not sure without testing that the index unused space goes in the unused column. The BOL assures us that the data space unused goes in the unused, but not sure about the indexes.
Business Accounts
Answer for Membership
by: chapmandewPosted on 2008-11-18 at 07:33:13ID: 22985479
Unused is the amount of space reserved for db objects, but not yet used.
unallocated is the amount of space that has not yet been reserved for db objects