I have database mirror set up in one of my servers and all seems to be fine, however, from time to time I get this error message in the logs:

--Execute it on Primary/Secondary server
EXEC xp_readerrorlog 0,1,"Error",Null, NULL, NULL, N'desc'

Database mirroring connection error 2 'Connection attempt failed with error: '10060 (A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)'.' for 'TCP://MIRROR_SERVER_DB01:5022'.

enter image description here

-- query that shows the current state of each database in the mirroring
  SELECT   db_name(sd.[database_id])              AS [Database Name]
          ,sd.mirroring_state                  AS [Mirror State]
          ,sd.mirroring_state_desc             AS [Mirror State] 
          ,sd.mirroring_partner_name           AS [Partner Name]
          ,sd.mirroring_role_desc              AS [Mirror Role]  
          ,sd.mirroring_safety_level_desc      AS [Safety Level]
          ,sd.mirroring_witness_name           AS [Witness]
          ,sd.mirroring_connection_timeout AS [Timeout(sec)]
    FROM sys.database_mirroring AS sd
    WHERE mirroring_guid IS NOT null
    ORDER BY [Database Name];

enter image description here


as it happens only sometimes, and I could not identify what else running at the same time - could have triggered this error,

what are possible things to look at while troubleshooting?

this server is hosted in the USA and I am not admin on the machine, only sysdba in the sql server world.

  • 1
    I would get with your sysadmin, and find out if there is anything going on around that time. Check the WEL(windows event logs). My gut feeling, and this is a pure stab in the dark without knowing all the details, is that your sysadmins are performing a snap of the server. Meaning, they are backing it up. When this happens the server goes into a freeze state for just a moment. Long enough for the secondary servers to freakout and throw error messages like the one you posted.
    – H.79
    Commented Dec 4, 2019 at 9:53


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.