locked
SQL 2005 Mirroring - when is database considered to be Online on mirror from reading messages in the SQL log RRS feed

  • Question

  • What message in SQL Error Log indicates that a mirrored database has failed and is online for service on failover?

    Had a server crash on primary and it went down for 2 hours, the application didn't work during this time. Investigating if it is that the failover didn't complete or if I should turn to look at the application side...

    We have a setup with principal,mirror and witness.

    We've had db-failovers before, but then all database servers have been Online. The app. has worked during these failovers...

    2010-02-09 15:42:07.290    spid51s    Error: 1479, Severity: 16, State: 1.
    2010-02-09 15:42:07.290    spid51s    The mirroring connection to "TCP://principal.server:5022" has timed out for database "db1" after 10 seconds without a response.  Check the service and network connections.
    2010-02-09 15:42:20.450    spid51s    Database mirroring is inactive for database 'db1'. This is an informational message only. No user action is required.
    2010-02-09 15:42:21.730    spid51s    The mirrored database "db1" is changing roles from "MIRROR" to "PRINCIPAL" due to Auto Failover.
    2010-02-09 15:42:22.150    spid51s    Recovery is writing a checkpoint in database 'db1' (11). This is an informational message only. No user action is required.
    2010-02-09 17:35:42.760    spid51s    Database mirroring is active with database 'db1' as the principal copy. This is an informational message only. No user action is required.


    At what point is it considered to have failed over?

    2010-02-09 15:42:21.730    spid51s    The mirrored database "db1" is changing roles from "MIRROR" to "PRINCIPAL" due to Auto Failover.

    or

    2010-02-09 17:35:42.760    spid51s    Database mirroring is active with database 'db1' as the principal copy. This is an informational message only. No user action is required.


    My interpretation is that it's Online on mirror server at 15:42 and that the mirroring is active again when former principal server came online again. Correct ?

    Thursday, February 18, 2010 8:52 AM

Answers

  • Yes you are right .

    At 15:42 the principal has failed over to mirrored instance and once the original principal is online at 17:35 the failover has happened to retain the original setup.


    Thanks, Leks
    • Marked as answer by axelssonn Monday, February 22, 2010 9:01 AM
    Thursday, February 18, 2010 7:57 PM
    Answerer