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.

Greg Askew
  • 34,339
  • 3
  • 52
  • 81
Paul Jones
  • 1
  • 1
  • How much free space is on the hard drive where the database and/or log resides? – Greg Askew Apr 25 '12 at 16:25
  • Have you tried creating a clustered index on one of your tables? http://msdn.microsoft.com/en-us/library/ms190273.aspx – the-wabbit Apr 25 '12 at 17:35
  • 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
  • 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
  • Yes, most if not all of the tables in this database have clustered indexes on them. – Paul Jones Apr 25 '12 at 17:47
  • You should have posted this as an edit to your original question -- it is not an answer... – jscott Apr 26 '12 at 15:52

2 Answers2

1

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

Jason Cumberland
  • 1,559
  • 10
  • 13
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?

Chris McKeown
  • 7,128
  • 1
  • 17
  • 25