Can we shrink datafile of a database which is configured with log shipping
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?
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.
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.
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.
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.
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.