I have dropped one huge table from the production DB

Current DB size on disk = 264 GB

The real size of the Tables = 32.164 GB

I am going to shrink this DB. Although it is not recommended, but this is the only option i have to reclaim this space, i will do monthly archiving for this table so i am sure it will not grow to this size again

My Questions are

There are Mirroring and replication on this DB, What should i do?

Is there a way to do this with no downtime? if no, what should be the plan to do this with the minimal downtime

Is Shrinking just affect indexes or it has other impact on performance?

  • 1
    you can use Shrink_DB_In_Chunks.sql. Do watch out for Indexes getting fragmented and perform this action during a maintenance window. – Kin Shah Sep 25 '15 at 23:11

Shrinking can have a massive impact on the transaction log, which in turn can dramatically impact the performance of mirroring and, I think to a lesser degree, replication.

My suggestion is: don't try to wave some wand and recover 200+GB right now, but instead, do it in chunks, gradually. Every night or every 6 hours or on some other reasonable interval, run one of these commands:

-- day 1
DBCC SHRINKFILE(data_file_name, 245000);

-- day 2
DBCC SHRINKFILE(data_file_name, 232500);

-- day 3
DBCC SHRINKFILE(data_file_name, 225000);

Yes, that is less satisfying, and obviously takes longer, but who cares? How badly do you need that space right now? Your features and users will be better off without that massive disruption IMHO.

An alternative would be to turn off mirroring and/or replication, and reinitialize them completely once the shrink operation is done (and you have backed up the log and made it the normal size, too). But I think just shrinking a bit at a time is far less complicated, disruptive, and risky.

  • Thanks your idea sounds good, are the Replicated and mirrored will get shrank as well – sebeid Sep 25 '15 at 20:44
  • Mirrored yes, because it replays everything in the log (including data movement), replicated no because it only re-runs DML AFAIK. – Aaron Bertrand Sep 25 '15 at 20:45
  • 1
    Doesn't matter if you do it in chunks or not. If you move data around, yes, it can affect indexing. In some scenarios it can make it worse, in others it can coincidentally make it better (it's theoretically possible that fragmented data could be organized better after it is shuffled around, but that certainly isn't a goal of the shrink operation, and is in fact extremely unlikely). You'll want to do proper reorganization (which means ensuring there is even fragmentation to worry about!) alongside any data file size adjustments. – Aaron Bertrand Sep 25 '15 at 21:10
  • 1
    The problem is, this becomes a serious never-ending game of cat and mouse. In order to reorganize and adjust the index so it's better organized, it needs to make room to write out more pages. This is at odds with your constant shrinking of the file. So, I would only worry about this if fragmentation is a proven problem. Or, I would just pick a satisfactory shrink target (say, 75 GB), and once you get to that point (not bothering to do it after every shrink operation), reorganize all of your indexes once or perhaps, in some cases, rebuild. – Aaron Bertrand Sep 25 '15 at 21:12
  • 1
    @sebeid I don't agree that Brent's headline fits every scenario, but I do agree that there are cases where fragmentation just doesn't matter at all: brentozar.com/archive/2012/08/sql-server-index-fragmentation Also some background worth checking out (though I confess I haven't read it): simple-talk.com/sql/database-administration/… – Aaron Bertrand Sep 25 '15 at 21:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.