July 15, 2008 at 7:48 am
I have a database which is growing by 500mb per day (currently 420gb).
Most of this growth is due to images which are being added, only to be deleted at a later date (no, I can't do anything about the design of the system, I'm stuck with it as it is!).
I've seen the KB article (Q324432) and other posts explaining that the free space from deleting blob data isn't actually freed up for re-use. I've also seen the workarounds:-
BCP out/Truncate/BCP in (or variations of this). Unfortunately, Merge Replication and the fact that it is a 24/7 system makes this all but impossible.
Moving the data to a new file and shrinking using the EMPTYFILE option. I tested this on a half size test database, and it took 2 weeks with no users attached, no other database activity etc. I've no idea what the performance hit would be if this was to be done while the database was in normal day to day use.
Does anybody have any other suggestions?
July 15, 2008 at 8:45 am
Perhaps put them in a separate filegroup? Maybe then work with that separately?
Or add an "image" filegorup, put the data in, then add another one at some point, delete the first one if it's empty?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
July 16, 2008 at 1:59 am
Or add an "image" filegorup, put the data in
That's the bit I'm worried about... moving the data to the new filegroup.
It took 2 weeks in development with no users on the system. I couldn't guarantee there would be no disruption when doing it in production, which is why I'm looking for alternatives.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply