I lose communication between my clusters almost 1 am to 1.30 am and it shows errors like 35206 on primary, 976 on clusters, sometimes the cluster is removed due to lost heartbeat leading to 1135 error , 1069 error. I have a strong feeling that my connection breaks everyday at that time but im clueless where to start kind of lost. any help will be greatly appreciated.
Add a comment
|
1 Answer
Have you checked SQL error logs? Start with what the description of errors. Is it because it lost quorum? Is there high CPU in any of the nodes? Are you on FCI or AAG?
We had to adjust timeout between our replicas when the databases were reporting unhealthy during high peak ETL loads.
0 down vote Set up trace to investogate what other things are running on the server. Then check SQL wait stats. https://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/
If there is no load - increase your timeout to a higher value (https://blogs.msdn.microsoft.com/alwaysonpro/2014/06/03/connection-timeouts-in-multi-subnet-availability-group/)) and see if the errors go away. However, if you have no problems in your workload then you should not worry about these errors. First you need to assess if users are complaining of slowness, are there any jobs failing during this time? You have to ask these questions before trying to simply reposlve an error.
-
We are on AAG. Normally everyday around 1 am we see error 35206 that is connection timeout with the replica which prompts error 976 on the secondary...Today the lost connectivity with the secondary remained long enough which the node to be removed from the active node. IF the timeout is short then it shows 976 on the secondary and if long enough it shows 1135 (lost node error). Im not sure where to start Commented Oct 26, 2017 at 15:07
-
The loads are high as frequently the system freezes that time . Commented Oct 26, 2017 at 15:08
-
Normally the connection timeout occurs and reconnects within 20 seconds but today it came after 3 minutes Commented Oct 26, 2017 at 15:29
-
Which version of SQL are you on? What is your timeout set at? What is SQLserver waiting on during those errors? DO you see healthy/unhealthy alerts in databases when you receive this error? Are there any job failures during the error? are there any high loads/ETL loads during the time of error? We had a similar problem in our environment during high ETL loads - the Dbs were available but reported unhealthy/ealthy alerts. We simply increased the timeouts to 100/200 since the alerts were acceptable during these high peak loads.– IMADBACommented Oct 26, 2017 at 15:40
-
SQL server 2012, timeout at 10 seconds. No Job failures during those times. System is on high load from 12 am and onwards ...Just around 1 am almost everyday the primary node faces a connection timeout problem with the other nodes. Commented Oct 26, 2017 at 16:12