0

I recently deleted some data from a table that was not needed and growing to too big a size with this query which provided to me by a support tech...

 declare @mdate datetime 
 set @mdate='04/04/2014' 
 delete from CacheDiffResults where CacheID in (select CacheID from ComparisonCache
 where TimeStamp < @mdate) 
 delete from ComparisonCache where TimeStamp < @mdate 
 delete from CacheDiffResults where CacheID not in (select CacheID from ComparisonCache) 

so it deleted all data before 04/04/2014. After I ran this query I saw that I gained back about a Gig of space from this table, then I ran a shrink on the database.

When I looked at the properties of the database the figures didn't add up? I'm no DBA (Networks) but I don't understand why the DB size and space left figures add up to 4G which is the size of SQL express 2005 DB?

Have a look at these screen shots...

enter image description here

As you can see 2569.73MB + 43.96MB != 4G

enter image description here

And what are these figures in the bottom picture? These don't add up to 4G either? Confused. Any help appreciated. TIA!

4
  • Where are you getting the 4GB size from? – LowlyDBA - John M Nov 20 '14 at 16:41
  • technet.microsoft.com/en-us/library/ms345154(v=sql.90).aspx Introduction SQL Server Express is a free and easy-to-use database product that is based on SQL Server 2005 technology...blah blah blah... In fact, it is differentiated from the rest of the SQL Server 2005 editions only by the following: •Lack of enterprise features support •Limited to one CPU •One GB memory limit for the buffer pool •Databases have a 4 GB maximum size – yermander Nov 20 '14 at 16:48
  • Oh, I see your confusion. The maximum size the database can be is 4GB, but that doesn't mean it will be 4GB by default. So yours is 2.5GB and can grow 1.5GB to hit a ceiling of 4GB. You won't see your size - 4GB = space remaining – LowlyDBA - John M Nov 20 '14 at 17:03
  • You might run DBCC UPDATEUSAGE (DB) as SSMS will sometimes get out of sync for those numbers.` – user507 Nov 20 '14 at 17:38
1

On the question of the size of things, first of all you said that you shrank a database, so that would reduce the DB size of the space that was discarded during the shrink. John M also provided some clarification.

Regarding the numbers in the bottom picture, they are:

select (2585832 / 1024.0) /* KB from the Reserved in the bottom picture to MB */ 
       + 43.96 /* MB free */ 

SpaceAllocated
---------------
2569.186562

So you can see that 2569.19 MB is essentially the same as 2569.73 MB

The other numbers in the bottom picture are showing you how that database is allocated:

reserved        data         index_size  unused 
-----------     ----------   ---------   --------- 
2585832 KB   =  2315184 KB + 137216 KB + 133432 KB

So it is pretty straight-forward and actually gives you some useful insight into your space utilization.

Additional Notes: Yes, when you shrink a database, it actually compacts the data and then releases the no longer used disk space. Of course, SQL Server 2005 Express can have many databases that are 4 GB. (If you update to a more recent version of SQL Server Express, the limit is 10 GB per database, not including the log space.)

Within a database the unused column in the example, defines how much space currently exists in the database file that could be used for adding more data. However, the database can be expanded to its maximum size or you can set autogrowth to allow it to grow in selected increments. (Use exact growth values, not percentages.)

3
  • Thanks for taking the time to answer! Can I ask what does this mean?... "first of all you said that you shrank a database, so that would reduce the DB size of the space that was discarded during the shrink" Was there actually any space discarded? Does this mean that I have actually lost some space? Also = 2315184KB + 137216KB + 133432KB = 2.46GB so I'm guessing I have ~ 1.6GB space available? According to top screen shot what is the 43.96MB unallocated? And instead of 133432KB unused I would expect to see unused = 1.5GB could you provide any clarification this. Thanks again! – yermander Nov 21 '14 at 14:14
  • I would upvote but dont have the reputation points yet!! – yermander Nov 21 '14 at 14:16
  • I can see from msdn.microsoft.com/en-us/library/ms188776.aspx that unused = Total amount of space reserved for objects in the database, but not yet used. And unallocated space = Space in the database that has not been reserved for database objects. So my interpretation of this would be that the only space I could use would be unallocated space? – yermander Nov 21 '14 at 14:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.