I've been reading all the articles (and comments) linked from Stop Shrinking Your Database Files. Seriously. Now. I understand that shrinking is bad.
What I have a hard time figuring out is how to do it right when its actually required.
The backstory is that I have inherited this archive DB with a handful of tables containing PDF's that should have been expired after 5 years, but the procedures to do this on a schedule was never set up. I don't have the row counts at the moment but it's a few million, and about 3TB. I guesstimate about half of this will be deleted and never come back.
I've been talking to our infrastructure providers DBA, but I find the advice they give (delete, reindex then shrink) to be highly dubious. I'm a system consultant not a DBA, but it seems I get to play one this time :-/
- Create a new filegroup
- Move all affected tables and indexes into the new filegroup using the CREATE INDEX … WITH (DROP_EXISTING = ON) ON syntax, to move the tables and remove fragmentation from them at the same time
- Drop the old filegroup that you were going to shrink anyway (or shrink it way down if its the primary filegroup)
Unfortunately I don't have access to the actual server just SSMS, and this is quite a bit outside my comfort zone anyway. I'm not actually a DBA..
DBCC INDEXDEFRAG or ALTER INDEX … REORGANIZE.
Yes but how.. there are quite a few parameters to choose from, and this seems optimized to get the size absolutely minimal, and not what I need which is to get rid of the bloat while keeping the DB as healthy as possible.
In other words I don't care if the DB grows some, it needs space to operate and it will grow some as document production increases year by year, but the nightly, weekly or monthly purging will now keep this in check.
I should also add that I can take this DB offline at night without much trouble.