'Space Available' question

We are upgrading tables, stored procedures and views in a sql server database. One of the enhancements involves clearing almost all of the records in the database. The database contains healthcare claim data. The Deletes removed the claims and cleared tables with aggregated data.

Prior to the upgrade teh database reported a 'Size' of 52 gigs with about 14 gigs of 'Space Available'.

Post upgrade the database reports a 'Size' of 48 gigs with about 38 gigs of 'Space Available'.

We're hoping to get the database to 'give back' some of the 38 gigs of space it is holding. In order to accomplish this we have tried:
1) the maintence utility that 'gets back' space -- 1 or 2 gigs impact.
2) reindex and run space utility -- no impact.

Other Constraints. It is a production database for which we do not have an exact replica in test. It would be a significant effort to move the db to a secure test environment. Translation: We have to be careful what we 'try' in terms of fixes. We can certainly go to a backup but it is not a test environment.

And advice or suggestions that folks might have in terms of how to manage 'Space Available' are appreciated.

Regards

rayko

Hi rayko,

You did not indicate if you had autogrow enabled on this database.

If that's the case, and you don't have the counter-part 'shrink' then this is the behavior you should expect.

Can you clarify "give back" in case I have mis-interpreted what you meant?

Thanks,

Terrence Nevins

SQL Server Program Manager

|||

hi,

please try to run dbcc shrinkdatabase

and dbcc shrinkfile to specifically shrink the files that has a lot

of free space and to specifically shrink the transactio logs

for more info please see BOL

thanks,

joey