sp_spaceused - unused

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.

LVL 21
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

so, for yours, this makes sense:

size = 24 GB, unallocated = 5 GB, reserved = 19 GB, data = 10 GB, index size = 9 GB, unused = 0

24 GB total

5 is unallocated (not reserved)

19 is reserved (10 for data, 9 for indexes)

19 + 5 = 24
mastooAuthor Commented:
Hi chapmandew, and thanks for the post .  Yes, those numbers all add up.  My question though is, how would one know that after the 50% free space index rebuild there is an additional 15 GB (34 - 19) of reserved space but that space is all "empty"?
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Sorry...I guess Im not exactly sure what you're asking...
mastooAuthor Commented:
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?
DBAduck - Ben MillerPrincipal ConsultantCommented:
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_stats and give it an index.
mastooAuthor Commented:
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.
DBAduck - Ben MillerPrincipal ConsultantCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mastooAuthor Commented:
Yes, they reconcile.  You reach the point I'm at where I conclude the index unused space isn't included in the "unused" number, which makes it fairly useless.  Argh.  I'll move on and accept.  Thanks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.