locked
MSSQL service restart on principal whilst mirroring... RRS feed

  • Question

  • I have a problem that has happened a couple of times now.  I'm in the process of testing our 2005 deployment - Standard edition on 2003 x64 servers for principal and mirror and 2005 express witness.  All are pre SP1.  Two databases are being mirrored.

    I have successfully set up mirroring which seems to work fine - but when I restart the MSSQL service on the principal, spurious things happen:

    The server that *was* the principal before restart

    1. database A is in (Mirror, Disconnected / In Recovery) state
    2. database B is in (In Recovery)

    The server that *was* the mirror:

    1. database A is in (Principal, Disconnected)
    2. database B is in (Principal, Disconnected)

    I can connect to both servers via Studio Manager and when I try to go to the current principal database to stop mirroring, the Studio Manager hangs and becomes non-responsive.

    Any ideas?

    Thanks

    Thursday, April 20, 2006 12:11 PM

Answers

  • That's RTM. Go ahead and try SP1.

    Sounds like you hit a known bug.  There were a couple of bugs the testers found during "long haul" stress runs where the state machines got out of whack (yes, that is a technical term.  ).

    Let me know if you have problems on SP1.

    Thanks,

    Mark

    Thursday, April 27, 2006 8:41 PM

All replies

  • What version of SQL are you using?

    Sounds like a bug. Try restarting the previous principal process again. Also, look in the errorlogs on that server.  It should provide more information.

    Thanks,

    Mark

    Thursday, April 20, 2006 11:37 PM
  • Thanks for responding Mark.

    Microsoft SQL Server 2005 - 9.00.1399.06 (X64)
    Oct 14 2005 00:35:21
    Copyright (c) 1988-2005 Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)

    I attempted to reproduce the funny behaviour again by restarting the principal server, when it came back up the mirror had taken on principal for both databases but they were initially in the Disconnected state.  Fairly shortly though the smaller of the two databases became synchronised. 

    Ten minutes later the larger database still hasn't synchronised - which is odd as there isn't any activity on this sql server as it is still in testing.  The mirror database is just In Recovery.  Now when I open the mirroring properties and try to stop mirroring from the disconnected principal, the dialogue box hangs after confirming that I do want to stop mirroring.  I have to close the dialogue box which then kills Management Studio.

    I then restarted the service on the previous principal and now the database state has changed from In Recovery to Mirror, Disconnected / In Recovery.  Restarting service on previous principal again doesn't change anything.

    In order to get the databases into any sensible state I had to restart the service on the new principal.  This had the effect of

    1. failing over the smaller mirrored and sync'd database to the other server
    2. doing something that brought the larger, disconnected database back into sync and mirrored happily again.

    However, although I now have two mirrored databases one of them is principal on Server A and the other is principal on Server B!!

    Ed

     

    Friday, April 21, 2006 9:24 AM
  • That's RTM. Go ahead and try SP1.

    Sounds like you hit a known bug.  There were a couple of bugs the testers found during "long haul" stress runs where the state machines got out of whack (yes, that is a technical term.  ).

    Let me know if you have problems on SP1.

    Thanks,

    Mark

    Thursday, April 27, 2006 8:41 PM