I posted a question about 'space available' earlier to this board (how much should i shoot for) adn got some very helpful replies.
Now a new '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.
The numbers you are looking at may be a tad off. Run DBCC UPDATEUSAGE(0) in the database to true up the size numbers. This may take a while, but I believe it will not hurt you as far as performance or locking tables. After that is done, I would expect you have less than 38GB free.
If you happen to have the 38GB free, and absolutely need to reclaim space, then you may be stuck with having to bcp out the data, drop a table (or two), shrink the database, then import the data back into the database. This will require a bit of downtime, depending on how big/numerous the tables that are left are.
1) space in teh transaction log. How do i check that? That sounds like an 'easy' fix.
2) dbcc shrinkfile and dbcc shrinkdatabase make me nervous. I have a, perhaps unfounded, perception that these are 'not smart' utilities and may not be the best way to manage 'space available'. This could very well be nothing more than my own ignorance.
Use this to see which files/filegroups are being used, or are just taking up space:
select fileproperty(f.name, 'spaceused')/128 as "UsedSpace MB", f.size/128 as "FileSize MB", isnull (g.groupname, 'LOG') as "FileGroup", f.name as "FileName"
from sysfiles f left join sysfilegroups g on f.groupid = g.groupid
Be careful about the transaction log, as the space used in it will grow until the next transaction log backup if the database is in full recovery mode.