sp_spaceused - unused
Posted on 2008-11-18
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.