?
Solved

sp_spaceused - unused

Posted on 2008-11-18
9
Medium Priority
?
1,030 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:mastoo
  • 4
  • 3
  • 2
9 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 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

0
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 450 total points
ID: 22985489
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
0
 
LVL 21

Author Comment

by:mastoo
ID: 22985699
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"?
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 60

Expert Comment

by:chapmandew
ID: 22985722
Sorry...I guess Im not exactly sure what you're asking...
0
 
LVL 21

Author Comment

by:mastoo
ID: 22986032
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?
0
 
LVL 25

Expert Comment

by:DBAduck - Ben Miller
ID: 22989968
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.
0
 
LVL 21

Author Comment

by:mastoo
ID: 22995678
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.
0
 
LVL 25

Accepted Solution

by:
DBAduck - Ben Miller earned 1050 total points
ID: 23001403
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.
0
 
LVL 21

Author Comment

by:mastoo
ID: 23005177
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.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Integration Management Part 2
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question