0

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.

Improve this question
6
  • How much free space is on the hard drive where the database and/or log resides?
    – Greg Askew
    Apr 25, 2012 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, 2012 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, 2012 at 17:35
  • Yes, most if not all of the tables in this database have clustered indexes on them.
    – Paul Jones
    Apr 25, 2012 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, 2012 at 15:51

2 Answers 2

Reset to default
1

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

Improve this answer
1

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?

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .