SQL Server – How to Shrink a Mirrored and Replicated Database

mirroringreplicationshrinksql server

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?

Best Answer

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.

Related Question