locked
Incorrect Alert Raised: Connection With Mirror Lost WHEN state change was to Syncrhonizing RRS feed

  • Question


  • Observations:

    1.        Strange / Incorrect Alert Recordings
      1.        As can be seen from the Screenshots in the Word Document – the Windows Event Log reported a TCP/IP Connection Failure
      2.       The Alert is set to monitor a State of 6 “Connection with Mirror Lost”
      3.        The Mirror monitor however shows that there was a state change but change was as follows:
        1. i.      06:00 – State = Synchronized
        2. ii.      06:03 to 06:07 – State = Synchronizing
        3. iii.      06:08 Onwards – State = Synchronized
      4.       NO “Mirror Lost” State in the Mirror Monitor
    2.        The AppLog SP Cleanup ran at that time – as can be seen by the Negative Growth in Table Size at 06:00

    Possibilities:

    1.        The Mirror Connection Lost Alert was incorrectly trigger by a State Change (Synchronized to Synchronizing to Synchronized)
    2.        The Transaction Log Backlog was 8MB at the time of the Alert
    3.        The SSMS connection to the Mirror at the time of the Alerts as active and operational
    4.        The Mirror Monitor showed the State to be “Synchronizing” and NOT Lost

    Thanks Howard Diesel

    I have a Word Document with all the screenshots - not sure how to attach
    Thursday, September 4, 2014 7:04 AM

Answers

  • Just some initial feedback - I believe the problem has been resolved after trying several things:

    1. ALTER DATABASE ... SET PARTNER TIMEOUT didn't work

    2. We set up a continuous PING - no drop in PING and very little response times over 10ms

    3. Found an error on the Mirror saying the network name could not be resolved

    We then discovered that the Mirrors conection with the DNS server was playing up and the Mirror was going into a Disconnected state.

    This then caused the "No Response" from the Principal

    Yesterday we added a HOST file entry and we didn't experience any problems


    Thanks Howard Diesel

    Thursday, September 11, 2014 6:17 AM

All replies

  • Hi Howard,

    Based on your description, could you please post the full error message about TCP/IP Connection Failure?  You can use the "Insert image" icon to upload the screenshots as follows. 



    Actually,  at the start of a database mirroring session, the database is in the SYNCHRONIZING state. The principal server is serving the database, and the mirror is trying to catch up. Once the mirror has caught up, partner databases enter the SYNCHRONIZED state. Refer to this article: Mirroring States (SQL Server).

    However, the mirroring state on your server changes from Synchronized to Synchronizing , and alert raises the message “Connection with Mirror Lost”, it indicates that principal server instance cannot connect to the mirror. Therefore, we need to verify if the mirror server is available between 06:03 and 06:07, or if there are some changes of database mirroring settings. I recommend you check SQL Server error log to get more detailed information.


    Thanks,
    Lydia Zhang


    Friday, September 5, 2014 9:03 AM
  • SQL Error LogMirror Monitor History

    Thanks Howard Diesel

    Friday, September 5, 2014 12:37 PM
  • Hi Lydia

    Thanks for the response - fortunately i was connected to the server at the time (via SSMS) and it was responded to queries

    An Archive process is was running that was deleting a 100k rows from an Application Log when the problems started

    I stopped the SQL Agent Job and the mirror started recovering


    Thanks Howard Diesel

    Friday, September 5, 2014 12:39 PM
  • Hi Lydia

    I think there is some confusion here - I don't see the possible answer?

    I am showing in the Mirror Monitor Screenshot that there was NO STATE CHANGE to show that the CONNECTION was lost!

    Rather it is showing the state change to SYNCHRONIZING


    Thanks Howard Diesel

    Saturday, September 6, 2014 9:24 AM
  • Hi Lydia

    I think there is some confusion here - I don't see the possible answer?

    I am showing in the Mirror Monitor Screenshot that there was NO STATE CHANGE to show that the CONNECTION was lost!

    Rather it is showing the state change to SYNCHRONIZING


    Thanks Howard Diesel

    Hi Howard,

    Refer to this article, in the Database Mirroring History dialog box, the possible mirroring states are as follows.

    ◦Unknown
    ◦Synchronizing
    ◦Synchronized
    ◦Suspended
    ◦Disconnected
     
    According to the error 1479, it indicates that the Mirrorred instance doesn’t receive any responses to ping for 10 seconds from Principal server. It is caused by network latency. To work around this issue, we can increase the timeout value from default 10 seconds to higher value via the following T-SQL. For more details, please review this blog: Adjusting the automatic failover time for SQL Server Database Mirroring.

    ALTER DATABASE dbName SET PARTNER TIMEOUT 20



    Thanks,
    Lydia Zhang


    Tuesday, September 9, 2014 2:52 AM
  • Hi Lydia

    Thanks for the response - I have made the changes to the appropriate databases this morning.

    we have also setup a CONTINUOUS PING during the period that it is happening to check 

    We have got the network guys monitoring - they sent the results this morning - there was no connection failure at the times the alerts were raised this morning


    Thanks Howard Diesel

    Tuesday, September 9, 2014 5:23 AM
  • Just some initial feedback - I believe the problem has been resolved after trying several things:

    1. ALTER DATABASE ... SET PARTNER TIMEOUT didn't work

    2. We set up a continuous PING - no drop in PING and very little response times over 10ms

    3. Found an error on the Mirror saying the network name could not be resolved

    We then discovered that the Mirrors conection with the DNS server was playing up and the Mirror was going into a Disconnected state.

    This then caused the "No Response" from the Principal

    Yesterday we added a HOST file entry and we didn't experience any problems


    Thanks Howard Diesel

    Thursday, September 11, 2014 6:17 AM