Sql-server – Where’s the missing Sql Server Express 2005 Database space? Size and space available dont add up to 4G

database-sizesql-server-2005sql-server-express

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!

Best Answer

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.)

Related Question