none
Mirroring

    Question

  • Hi,

    I have a mirroring setup in which
    S1 is principle
    s2 is mirror
    s3 is witness

    there are 7 databases that are mirrored between S1 and S2,
    suddenly today I found that the DB1 on S1 went into retoring mode though it was still acting as principle. The failover didnot happen.
    due to this the whole site for the database was down.
    When I see the error log I see the following

    The mirroring connection to "TCP://<Witness Server>2" has timed out for database "DB1" after 10 seconds without a response.  Check the service and network connections.

    During this time there was high CPU Utilization in the range of above 95% for the server containing the principle datbase.

    I am not able to conclude on, what could be the reason for this timeout, I dont think it was because of the network connection for the witness server.

    Please let me know, waht could be the possible reasons for this issue.
    Could it be
    1: n/w connection OR
    2: High CPU utilization

    OR anything else that can be though of

    Also please note the issue happened only for one database out of all the 7

    Also I notice that one of the Jobs on the DB1 db is taking very longtime and when ever it is excuting the CPU utilization is high... could this be the potential reason for the connection timeout--> then DB1 going into restoring mode ?

    Friday, May 09, 2014 6:01 AM

Answers

  • What that job was running, have you checked the drives are they located on the SAN storage.

    Is the database which caused the issue is located on any other drive and any error message observed in event logs for the disk I/O not responding or any network related error as that may cause high CPU the issue. Please check and share

    Check below thread, seems to be similar issue, if helps

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/a483bf8d-0719-4869-8628-3a2af484ea09/network-timeout-to-mirror-causes-primary-to-deny-connections?forum=sqldatabasemirroring


    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Friday, May 09, 2014 1:05 PM
  • Generally one thing we have to aware that synchronous mode (High Safety) has 10 seconds time out value configured. This value is dependent on the ping request being sent between Principal and Mirror server.

    Sometimes the ping acknowledgement might come late because of various reasons to mention a few network congestion or slow processing, however this delay is considered as TIME OUT, and depending on the operating mode, it may failover BUT in your case you said it has not done any failovering so it is always recommended to be careful while configuring THE TIME VALUE.

    The TYPICAL TIME OUT VALUE (10 seconds) is good enough. But if YOU wish, we can increase this value by executing following command:

    ALTER DATABASE AdventureWorks --use your database name here which is DB1

    SET PARTNER TIMEOUT 15

    GO

    /*To OBTAIN the current time out value, execute the following query AND YOU WILL SEE THE NEW TIME OUT:*/

    SELECT mirroring_connection_timeout

    FROM

    sys.database_mirroring

    GO




    Friday, May 09, 2014 6:51 PM

All replies

  • What that job was running, have you checked the drives are they located on the SAN storage.

    Is the database which caused the issue is located on any other drive and any error message observed in event logs for the disk I/O not responding or any network related error as that may cause high CPU the issue. Please check and share

    Check below thread, seems to be similar issue, if helps

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/a483bf8d-0719-4869-8628-3a2af484ea09/network-timeout-to-mirror-causes-primary-to-deny-connections?forum=sqldatabasemirroring


    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Friday, May 09, 2014 1:05 PM
  • Generally one thing we have to aware that synchronous mode (High Safety) has 10 seconds time out value configured. This value is dependent on the ping request being sent between Principal and Mirror server.

    Sometimes the ping acknowledgement might come late because of various reasons to mention a few network congestion or slow processing, however this delay is considered as TIME OUT, and depending on the operating mode, it may failover BUT in your case you said it has not done any failovering so it is always recommended to be careful while configuring THE TIME VALUE.

    The TYPICAL TIME OUT VALUE (10 seconds) is good enough. But if YOU wish, we can increase this value by executing following command:

    ALTER DATABASE AdventureWorks --use your database name here which is DB1

    SET PARTNER TIMEOUT 15

    GO

    /*To OBTAIN the current time out value, execute the following query AND YOU WILL SEE THE NEW TIME OUT:*/

    SELECT mirroring_connection_timeout

    FROM

    sys.database_mirroring

    GO




    Friday, May 09, 2014 6:51 PM