locked
CHECKDB interrupts automatic failover RRS feed

  • Question

  • Thanks for reading this thread.

    We have about 20 databases on which mirroring are enabled.

    After our principal server rebooted due to windows updates at 4AM Sunday most of the databases in the principal server changed their status to "mirror". As we know that is normal. However, I discovered that the status of "PRICE" database in principal server didn't change, it means it is still "principal". I checked the log and abstracted the relevant ones as follow:

    19/12/2010 04:11:04 Starting up database 'PRICE'.
    19/12/2010 04:11:12 CHECKDB for database 'PRICE' finished without errors on 2010-12-12 06:33:47.597 (local time). This is an informational message only; no user action is required.
    19/12/2010 04:11:12 Database mirroring is active with database 'RPICE' as the principal copy. This is an informational message only. No user action is required.

    All the other databases which changed their status normally don't have the CHECKDB records in the log at that time. we did a integrity check on 2010-12-12 06:30. It seems the integrity check runs almost a week and it just ended when the server rebooted. I suppose it could be the reason why the PRICE database didn't fail over after its reboot.

    Please share your idea regarding this issue. Thanks.

    Regards,

    cn2500

    Tuesday, December 21, 2010 4:13 PM

Answers

  • This looks like a role synchronization issue. The failover seems to have occurred but the former principal server instance didn't complete role synchronization to assume the mirror role.

    You can confirm if the database failed over by looking into  the new principal server instance's errorlog during the time of the failover and looking for a message with the text "Automatic Failover" associated with the database name.

    Role synchronization messages can be printed in the SQL errorlogs by applying Cumulative Update 10 for SQL Server 2005 SP3. After you apply the fix on both instances, you need to enable the trace flags: 1439, 3605 to get the role synchronization messages printed in the SQL errorlog.

    Reference: http://support.microsoft.com/kb/983500


    This posting is provided "AS IS" with no warranties, and confers no rights.
    My Blog: http://troubleshootingsql.com
    Twitter: @banerjeeamit
    SQL Server FAQ Blog on MSDN: http://blogs.msdn.com/sqlserverfaq
    • Marked as answer by Alex Feng (SQL) Wednesday, December 29, 2010 12:18 PM
    Thursday, December 23, 2010 1:32 PM

All replies

  • Hi!

    I am not 100% certain, but as far as I remember on server startup all DBs are going through a recovery which will show up with a CHECKDB message in errorlog. Regarding the mirroring: I would first check if the config is correct... e.g. if the mirroring is set to High safety but has no Witness provided it will not failover automatically. It also won't if it's set to High Performance. A CheckDB NEVER stops a database from failing over if the old principal server is rebooted... How should it? The moment the server goes offline the mirror + witness will initiate a failover and the principal has no chance to do anything about it (because it is offline...)

    So my guess would be that someone forgot to configure the witness, or the witness for that particular database is not available during the reboot...

    Lucifer

    Wednesday, December 22, 2010 6:23 AM
  • Lucifer is correct. We print the message about last successful run in ERRORLOG during database startup.

    cn2500,
    Whats the message you see in SQL ERRORLOG?

     


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Wednesday, December 22, 2010 7:29 AM
  • Thanks for your reply.

    We have actually set all the databases as "High safety with automatic failover" for 1 year. This is the first time that a database cannot failover automatically when reboot. At that time both the mirror server and witness are fine. The strange thing is all other 20 databases failed over successfully except this one.

    Wednesday, December 22, 2010 10:31 AM
  • Thanks for your reply.

    Actually I have posted the rebooted serrver's errorlog above.

    19/12/2010 04:11:12 Database mirroring is active with database 'RPICE' as the principal copy. This is an informational message only. No user action is required.

    it should be:

    19/12/2010 04:11:12 Database mirroring is active with database 'RPICE' as the MIRROR copy. This is an informational message only. No user action is required.

    because it just rebooted. all other database changed to mirror copy except this one.

    Thanks.

    Wednesday, December 22, 2010 10:38 AM
  • Have you looked into the mirroring monitor for that DB? And into the SQL Error Log on the Mirror server? Either the mirror monitor will show a problem with the witness connection, or the SQL Error Log on the Mirror should tell you something about the failover attempt.

    Lucifer

    Wednesday, December 22, 2010 1:42 PM
  • This looks like a role synchronization issue. The failover seems to have occurred but the former principal server instance didn't complete role synchronization to assume the mirror role.

    You can confirm if the database failed over by looking into  the new principal server instance's errorlog during the time of the failover and looking for a message with the text "Automatic Failover" associated with the database name.

    Role synchronization messages can be printed in the SQL errorlogs by applying Cumulative Update 10 for SQL Server 2005 SP3. After you apply the fix on both instances, you need to enable the trace flags: 1439, 3605 to get the role synchronization messages printed in the SQL errorlog.

    Reference: http://support.microsoft.com/kb/983500


    This posting is provided "AS IS" with no warranties, and confers no rights.
    My Blog: http://troubleshootingsql.com
    Twitter: @banerjeeamit
    SQL Server FAQ Blog on MSDN: http://blogs.msdn.com/sqlserverfaq
    • Marked as answer by Alex Feng (SQL) Wednesday, December 29, 2010 12:18 PM
    Thursday, December 23, 2010 1:32 PM