From a recent conversation with a customer, I noticed even some of the experienced DBAs are not very clear about the terms “unallocated space” and “unused space” and by doing a quick Bing search, found there are many SQL friends out there trying to get a clear correlation between these SQL Server terms: unallocated space, unused space, and reserved.
Today, let us try to get a clear picture (infact, a picture!) of these terms using sp_spaceused system stored procedure and at the same time understanding results for database size information.
Execute the below query on AdventureWorks2012 sample database
USE [AdventureWorks2012] GO EXEC sp_spaceused GO
Results:
Now, let us understand the above result sets, lets check by the column names
- database_size: database size (data files + log files) = 205.75 MB
- unallocated space: space that is not reserved for use by database objects (Space Available) = 14.95 MB
- reserved: space that is reserved for use by database objects = 190.05 MB
- data: total space used by data = 97016 KB/1024 = 94.74 MB
- index_size: total space used by indexes = 88048 KB/1024 = 85.99 MB
- unused: portion of the reserved space, which is not yet used = 9544 KB/1024 = 9.32 MB
- used: let us coin this word to simplify things. say used = data + index_size = 94.74 + 85.99 = 180.73 MB
We now know what these columns are and the numbers they are returning. But, here is the real confusion part: How are they correlated with each other?
To answer this, below is the simple formulae I came up with to better interpret the results. I haven’t found this simple explanation clearly anywhere else on SQL space (trust me, you won’t)
(color coded to read easy)
used = data + index_size
reserved = used + unused
database_size = reserved + unallocated space + log space
To cross verify, we can substitute the result set we got from AdventureWorks2012 database in the above formula and confirm by checking the log file size. Alternatevely, we can calculate the log file size, which is not returned from the ‘sp_spaceused’ result set.
180.73 = 94.74 + 85.99 –>TRUE
190.05 = 180.43 + 9.32 –> TRUE
205.75 = 190.05 + 14.95 + log space
Hence log space = 205.75 – (190.05+14.95) = 0.75 MB (which is right!)
And, below is my small art in the same color coding as above to illustrate in a picture format. Of course, who doesn’t love pictures, and I am no exception -:)
Excellent description and a great graphic.
so what? how is unallocated space related to the Db size?
Thanks Paul!
@sql – Unallocated space considered part of total db size. Thanks!
Nice, thanks much!
Yes so if I am archiving and removing rows from my database. How would I measure the space I have saved? unallocated space + unused
Hi Kyle – that is correct, however there won’t be any change in unallocated space
Clearly explained.
So in this instance total db size in not the entire capacity of the database eg if we were using SQL express 2005 we’d have a 4GB capacity we would still have 3.8GB of space to use? Am I correct in saying this? And why even mention unallocated or unused space. What is the unused space reserved for?
Great post though, the best I could find!
[…] found this post is very information indeed. The author explain the SQL Server Size more easier to understand. When […]
when you say “reserved: Space that is reserved for use by data and log files” , I think its only the space reserved for data files not for log files as reserved+unallocated is only data file size, not the complete database size
Very nice article.Thanks for sharring the information.
Sorry, you have conflicting and inaccurate statements in your article. This just adds to the general confusion out there. The graphical diag is accurate enough, but your ‘yellow dot’ bullet points above it are not.
@Mark – what exactly do you mean by ‘yellow dot’ … troll?
Hi Mark, can you please elaborate what you think is inaccurate in the above article? Thanks
sp_spaceused will fail if there is a fullstop in the table name. Even if you put square parentheses around it. For example sp_spaceused [MyTable 1.0 – Inventory]
Very frustrating.
Was looking at shrinking my database file(test Server)and came across your wonderful explanation. Thank you! SO When I do a dbcc shrinkfile , is it the Unused space or the unallocated space that gets released back to the OS? or am I missing something here?
Oh Another question also would be how does the space get marked when I drop indexes on a Hugeee table and and also truncate that table? (data+index is 170GB) .– where does this space go??
I have a table that has a smaller ‘reserve’ than the ‘data’ + ‘index_size’:
exec sp_spaceused N’dbo.sys2part’:
name rows reserved data index_size unused
sys2part 35290 6560 KB 4616 KB 3912 KB 0 KB
What do you make of that?
@GK You are correct! thank you for the catch, updated the post.
@lakshmi Both unused and unallocated can be released back to the OS, make sure you use the correct Argument when using SHRINKFILE. However shrinking data files is not recommended
[…] SQL Server – Understanding ‘sp_spaceused’ Results for … – SQL Server – Understanding ‘sp_spaceused’ Results for Database Size Information. June 28, 2013 by Suresh Raavi […]
[…] SQL Server – Understanding ‘sp_spaceused’ Results for … – SQL Server – Understanding ‘sp_spaceused’ Results for Database Size Information. June 28, 2013 by Suresh Raavi […]
[…] SQL Server – Understanding ‘sp_spaceused’ Results for … – SQL Server – Understanding ‘sp_spaceused’ Results for Database Size Information. June 28, 2013 by Suresh Raavi […]
Great article. Clean explanation of something that Microsoft has obscured 😦
Great article. There is a Currently Allocated Space text box on Shrink File window, how do we understand that? It looks always equal to the file size which may be a little conflicted to the explanation of “unallocated”. Since allocated is the whole file but unallocated is part of the file and we can not say allocated + unallocated = file size(allocated).
Waiting 5 years to find this! Thankyou!
Hello there dataginger.com
Copy-Paste FAST CASH System + Software Banks $272 DAILY PROFITS With 100% FREE Traffic & Zero Experience… In LESS Than 20 Minutes Per Day
The FASTEST Way To Consistently Bank BIG Money Online
* Fast profits – start making $100-$200 daily in PURE PROFIT within just a few hours from right now
* Copy / Paste Simple – we GIVE you the campaigns PROVEN to drive massive commissions … all you do is add YOUR link
* Make The Big Money Most Only Dream Of – we use this EXACT method to effortlessly crank out $10 – $15 THOUSAND every single month
* 100% Beginner Friendly – zero previous experience or tech skills needed
* Plug & Play Software COPIES Our Most Profitable Campaigns For You – for multiple ‘set & forget’ income streams
* Easy To Scale To ‘Quit Your Job’ Income – in literally just a few minutes per day
* A-Z Traffic System Included – we’ll show you how to start with 100% free traffic, then how to scale up once you’re in profit
* CAN’T FAIL Process Proven For Over 2 Years
IF YOU’RE INTERESTED, CONTACT ME ==> profit_blistering@mail.com
Regards