Can we shrink datafile of a database which is configured with log shipping

Saketh.K 2016-08-19 07:09:07

Hello Everyone,

I have a primary database Server A and Secondary Database Server B Log shipping configured on A to B of a database,

1) Database size 500 GB, We would like to delete old data and make it to 250 GB, Can we do this without breaking of log shipping?

2) After this we need to shrink the data file not log file, Can we shrink data file without breaking of log shipping?

If we do shrinking do i need to stop log shipping jobs or will it break log shipping configuration?

Aaron Bertrand 2016-08-24 03:33:39
Do you have a test config where you can try this? Can't you just re-establish log shipping in any case?
Saketh.K 2016-08-24 06:06:57
I can re-establish Log-Shipping, But in case of shrinking of a data file the log file increase rapidly, so is there any chance without breaking of this can we shrink a database.
Aaron Bertrand 2016-08-25 01:16:47
That shouldn't break log shipping, it just won't be very fun to ship all those log changes. I still think you should stop thinking about this as a "let's not disrupt log shipping" exercise and instead focus on "let's make this operation efficient and least disruptive" – meaning disable log shipping, fix your data files (and fix your settings so you don't have to do this again), then re-establish log shipping. But again, this isn't really on-topic here.
Vlady Oselsky 2016-09-02 13:32:10
as a note: Always remember that shrink will almost always require index rebuilds and reorgs which can in return increase your t-logs are you are waiting for those operations to complete. Also here something for light reading https://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/ with links to other respected people in community.
HariMindi 2016-09-05 19:49:00
You can do it without breaking the logshipping and i had exactly a similar situation which worked without any issues.
If the logshipping secondary is in Standby mode, then the data file shrink get replicated. But the same is not possible if the logshipping is configured in Norecovery mode.