10 GB Limit

[pdd]

At our company we are using a proprietary third party custom application that uses SQL Server Express as its backend. As you are aware SQL Server Express 2008 has a hard 10GB limit. For the “old” data in this database, we have a separate database offsite to which we store the data.

I am aware that purchasing SQL Server Enterprise would solve this issue, but being a small company, this is not a solution for us at this point in time. We do not need “old” records to be stored in the “live” SQL Express database that is being used by the proprietary software. So I can delete the “old” records from the SQL Express database, but the problem I am experiencing is how to reclaim the space consumed by the “old” data after deleting this data. I don’t want to increase fragmentation by using the DBCC SHRINKDATABASE command. But any command I run, to rebuild the index, or shrink the database or the log files, doesn’t reclaim this space. The 10GB database is currently showing less than 100MB of unused space according to sp_spaceused. I appreciate any of your input or help with this issue from one drop of SQL to another.

 

[Lotok]

hmm, I thought SQL express only gave 4gb...

Anyway that aside, you are concerned that because the file size hasn't shrunk the used capacity hasn't? The file size is always larger than the contained data. When you set up a DB, you set up a growth rule to allow for that.

If you don't want to shrink the file, you could just leave it.

Sent from my XT910 using Tapatalk 2

 

[Lotok]

Slightly mis read your post but what I said is still true, the sp_spaceused shows allocated disk space for tables or whole Dbs. Can you confirm that is not the stat you are reading?

Sent from my XT910 using Tapatalk 2

 

[pdd]

hmm, I thought SQL express only gave 4gb...

Anyway that aside, you are concerned that because the file size hasn't shrunk the used capacity hasn't? The file size is always larger than the contained data. When you set up a DB, you set up a growth rule to allow for that.

If you don't want to shrink the file, you could just leave it.

Sent from my XT910 using Tapatalk 2

So, it's a 10GB database file, it says it is currently using something like 9059MB at this moment with 60MB unallocated. I'm aware that just like a real hard drive SQL Sever just marks the old space as being deleted, and the DBMS will use this marked space when it needs it. Atleast that is my understanding. I don't understand why the DBMS can't just report the right amount of free space.

What I'm asking for is a way to reallocate this "marked" space. I need it reallocated because if I want to rebuild indexes with ALTER INDEX REBUILD (that have become defragmented due to frequenct SHRINKDATABASE commands), then I need sufficient free space.

(SQL Express 2008 R2 increased the limit to 10GB.)

 

[pdd]

if you are referring to the "database_size" field from sp_spaceused then yes, it is at 9059.88 MB.