sp_spaceused, unallocated vs unused

Question

sp_spaceused on AdventureWorks gives me this.

database_name            database_size  unallocated space
------------------------------ ------------------ ------------------
AdventureWorks           172.00 MB          3.70 MB

reserved           data               index_size         unused
------------------ ------------------ ------------------ ------------------
170288 KB          88320 KB           77160 KB           4808 KB

Question 1: "unallocated space" means space taken up by the database that isn't filled up with data yet. Is that correct?

Question 2: What is "unused"?

Answer 1

Hi,

following link might be helpful.

http://sqlpost.blogspot.com.au/2009/07/sql-server-sql-daily-tips-space-unused.html

Rajitha

---

Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

Answer 2

Unallocated space is space that is in the database files that isn't assigned to an object (such as a table or index, etc).  Unused space is space that has been allocated to an object, but is not being used.  When an object requires additional allocated space, it is given either 8K (if the current allocated space is less than 64K) or 64K (for objects that have 64K or more already allocated to that object).

As a simplified example, if you have a table larger than 64K, and you insert a row that needs 1K and there is no room in the current allocated space in that table for that 1K row, SQL will take 64K from the unallocated space, and reserve it for that table.  Then use 1K of that reserved space for the new row.  So in that case, unallocated space would be decreased by 64K, reserved would increase by 64K, data would increase by 1K, and unused would increase by 63K.

If you insert a 1K row in a table and SQL can put that row into already reserved, but unused space, then unallocated space and reserved will not change, but data will increase by 1K and unused will decrease by 1K.

Tom 

Answer 3

I think I get it