locked
Database mirroring failover takes long time RRS feed

  • Question

  • Hi,

    In our production server we have setup the database mirroring with the witness server for the automatic failover. Last night when we did the maintenance on the primary server we shutdown the primary server around 10:19 PM. But the applications were not able to connect to the database until 10:36 PM on the mirror server. The logs files says as below. could you please let me know why it took that long to failover the database and bring the database online on the mirror server?
    Does this means SQL server took 17 min to failover the database to the mirror server?

    At 10:19 PM
    Database mirroring is inactive for database 'dbname'. This is an informational message only. No user action is required.

    At 10:24 PM
    The mirrored database "dbname" is changing roles from "MIRROR" to "PRINCIPAL" due to Auto Failover.

    At 10:24 PM
    Recovery is writing a checkpoint in database 'MonetraProd' (19). This is an informational message only. No user action is required.

    At 10:36
    Database mirroring is active with database 'MonetraProd' as the principal copy. This is an informational message only. No user action is required.

    Please let me know if you need any more information.


    kp
    Wednesday, February 24, 2010 8:27 PM
    Answerer

Answers

  • Use the below tsql to change timeout period to 10 secs from 10 mins

     

    alter database db_name set partner timeout 10

     

     

    The factors like network bandwidth and the amount of logfiles that needs to be sent to the mirror from principal

    also is a cause for slow failover.

    Check the network bandwidth around that period when you initiate a failover using network monitor toosl and

    For amount of logfiles depend on amount of transactions commited on principal and not being sent over to the

    Mirror until the point of failover.


    Thanks, Leks
    Thursday, February 25, 2010 3:55 AM
    Answerer
  • Here is a good link that I used when setting up my monitoring:

    http://msdn.microsoft.com/en-us/library/cc966392.aspx

     

    Saturday, February 27, 2010 4:18 AM

All replies

  • Please check what is your partner timeout configuration in seconds?

     

    The amount of time required to apply the log depends on the speed of the system, the recent work load, and the amount of log in the redo queue. (Basically there might have been lots of logs to be sent to mirror)

    Also the network availability is a major factor.


    Thanks, Leks
    Wednesday, February 24, 2010 9:43 PM
    Answerer
  • Just adding to David's reply.  It looks like your partner timeout is set to 600 so it waited 5 minutes to start the process.  It appears on the surface that you had a backlog of logs in the queue and/or a poor network connection.  You can alert on the backlog and set it to send you an email if it gets behind.
    Wednesday, February 24, 2010 11:45 PM
  • Thanks so much to both of you for your reply. Yes the mirror timeout is set to 600 sec. How do I set up the alert to send email when it gets behind? please let me know.

    Is there anyway to check the network performance?
    kp
    Thursday, February 25, 2010 3:46 AM
    Answerer
  • Use the below tsql to change timeout period to 10 secs from 10 mins

     

    alter database db_name set partner timeout 10

     

     

    The factors like network bandwidth and the amount of logfiles that needs to be sent to the mirror from principal

    also is a cause for slow failover.

    Check the network bandwidth around that period when you initiate a failover using network monitor toosl and

    For amount of logfiles depend on amount of transactions commited on principal and not being sent over to the

    Mirror until the point of failover.


    Thanks, Leks
    Thursday, February 25, 2010 3:55 AM
    Answerer
  • Here is a good link that I used when setting up my monitoring:

    http://msdn.microsoft.com/en-us/library/cc966392.aspx

     

    Saturday, February 27, 2010 4:18 AM