Sunday, November 18, 2012 1:46 PM
We run a database mirror for reporting purposes and we break the mirror every night for our ETL jobs.
The break process normally takes a couple of minutes, unless there is a large amount of work in the redo queue.
The process seems tonight seems to be taking much longer than normal its currently running
ALTER DB SET PARTNER OFF
When I check the performance counter I can see the "redo queue KB" is no longer showing so it has past that step but its still running the query.
I can see the following DBMirroring_CMD wait type, but I can find out the progress of how much longer it has to go or what its actually doing?.
Sunday, November 18, 2012 3:52 PM
All i can recommend you as of now is: --> to first let the DB MIrroring state be in Syncronized & redo_queue to be 0 before initiating Partner off.
Acc. to the scenario i think you are running mirroring in Synchronous mode and thts the reason it forces you to set partner off.
I think its best to either switch your Mirroring in asynchronous mode rather than setting partnet off everynight & set it up again.
Or you can also check the feasibility to make use of other techniques like log-shipping to avoid repetative manual work every night.
Sarabpreet Singh Anand
This posting is provided , "AS IS" with no warranties, and confers no rights.
Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.