Sign in
 
HomeLibraryLearnDownloadsTroubleshootingCommunityForums
SQL Server Developer Center >
> 
> 
SQL Server 2008 trouble shoot - SET PARTNER OFF

Answered SQL Server 2008 trouble shoot - SET PARTNER OFF

  • Sunday, November 18, 2012 1:46 PM
     
     
    Sign In to Vote
    0

    Hi Guys,

    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?.


    Troy

     

All Replies

  • Sunday, November 18, 2012 3:52 PM
     
     Answered
    Sign In to Vote
    0

    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

    Blog , Personal website

    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.