Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
How much free space is on the hard drive where the database and/or log resides? –  Greg Askew Apr 25 '12 at 16:25
    
There is 506GB free on a 698GB hard drive. That is a non-issue, the issue is SQL Express. I can't run ALTER DATABASE REBUILD because it will say there is not enough free space in the page to do it. I can't do anything to reclaim this space. :( –  Paul Jones Apr 25 '12 at 17:28
    
Have you tried creating a clustered index on one of your tables? msdn.microsoft.com/en-us/library/ms190273.aspx –  the-wabbit Apr 25 '12 at 17:35
    
Yes, most if not all of the tables in this database have clustered indexes on them. –  Paul Jones Apr 25 '12 at 17:47
    
Yes, I tried reorganizing but that doesn't reclaim the space that I believe is there either. I decided to delete even more rows to get to the point where i have enough free space to hopefully do a rebuild and I think that will fix it. –  Paul Jones Apr 26 '12 at 15:51
show 1 more comment

2 Answers

You will need to shrink the database if you want to reclaim the free space.

share|improve this answer
add comment

As Jason said, if you want the available space back, you'll need to shrink the database.

However, you say that SQL reports that there's only 100 MB of unused space. Presumably you're expecting this figure to be higher? If so, how much space would you expect to be able to claim back?

Reorganizing (rather than rebuilding) the indexes would take less working space. Have you tried that?

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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