locked
SQL WItness will not reconnect after failure RRS feed

  • Question

  • Hello,

    I have several mirrored databases in H/A using a witness (a hyper-v virt) all in the same domain.  Yesterday, the hyper-v parent failed and the witness was offline for a few minutes.  When it came back up, all my Principals and their mirrors are in state "DISCONNECTED" from the witness, though they are still actively synchronized themselves (principal <-> Mirror).  I cannot for the life of me figure out how to get the witness to re-connect with the mirrored databases.  I have rebooted the witness to make sure it came down clean and came back up.  All the services seem to be working and the following query in the witness shows the endpoint is negotiating:

        select * from sys.database_mirroring_endpoints

    This query in the principals shows the state of the databases as disconnected from the witness:

        SELECT * FROM sys.database_Mirroring

    My only other thought is to whack the mirroring and rebuild it again, but that's a huge pain in the backside.

    Any suggestions?

     


    Thursday, August 20, 2009 2:50 PM

Answers

  • Hi,

    On SQL server ErrorLog folder, you should find a trace profiler on each server : Miror, Secondary and the Witness.

    Could you run the query below to see what's happening :

    -- Before to run the query in SSMS, click on CTRL + T to put the result in Text format. 

    SELECT

          StartTime,DatabaseName,State,TextData

    FROM

          ::fn_trace_gettable('%sql errorlog path%\log_8.trc', default)

    WHERE

          EventClass = 167

    ORDER BY

          StartTime ASC

    Cheers,


    Michel Degremont. http://blogs.technet.com/mdegre
    Sunday, August 23, 2009 6:27 PM