Fixing Intermittent SQL Server Availability Group Connection Timeouts

Table of Contents

Intermittent connection timeouts within SQL Server Availability Groups can significantly disrupt data consistency and high availability. This article provides a comprehensive guide to diagnosing and resolving these challenging issues, ensuring the robust operation of your SQL Server environment. Understanding the symptoms, interpreting error messages, and systematically investigating potential causes are crucial steps in maintaining a healthy Always On Availability Group. We will explore both application-side and network-related factors that contribute to these connection instabilities.

Understanding Intermittent Connection Timeouts in SQL Server Availability Groups¶

SQL Server Always On Availability Groups provide a high-availability and disaster-recovery solution that ensures your databases remain operational and accessible. This technology relies on constant, stable communication between replica servers. When this communication is intermittently disrupted, it leads to connection timeouts, which can manifest in various problems for your database environment.

The Critical Role of Availability Groups¶

Availability Groups are designed to automatically fail over a group of user databases, known as availability databases, from a primary replica to a secondary replica if the primary replica becomes unavailable. This process depends on a continuous and reliable flow of data and heartbeat signals between the replicas. Any interruption in this communication can compromise the integrity and readiness of the Availability Group. Identifying and addressing these intermittent issues is paramount for maintaining business continuity and data integrity.

What Are Intermittent Connection Timeouts?¶

Intermittent connection timeouts occur when one Availability Group replica fails to receive a response from its partner replica within a predefined period. This period is governed by the SESSION_TIMEOUT setting for each replica. When a timeout occurs, the connection is considered lost, triggering a series of events and logging specific error messages. These timeouts are often transient, making them particularly challenging to diagnose without systematic data collection.

Symptoms and Impact of Connection Timeouts¶

The effects of intermittent connection timeouts can range from minor data staleness to severe disruptions in automatic failover capabilities. Recognizing these symptoms early is key to initiating effective troubleshooting. A seemingly minor network glitch or a brief server overload can cascade into significant operational problems if left unaddressed.

Data Latency on Secondary Replicas¶

One immediate consequence of connection timeouts is increased data latency on secondary replicas, particularly those configured for read-only workloads. When the connection between primary and secondary replicas is broken, data changes on the primary database cannot be immediately synchronized to the secondary. This means that queries executed against secondary replicas might return stale data, leading to inconsistent reporting or application behavior. Such discrepancies can undermine the value of read-only secondary replicas.

The degree of staleness depends directly on the duration and frequency of these connection interruptions. Even short, intermittent disconnections can accumulate, causing the secondary replica to fall significantly behind the primary. Applications relying on real-time data from secondary replicas will be particularly affected by this latency.

Availability Group Synchronization State¶

The Always On dashboard in SQL Server Management Studio (SSMS) is a primary tool for monitoring the health of your Availability Groups. Intermittent connection timeouts will often cause the dashboard to report replicas in a “Not Synchronizing” state, even if only for brief periods. This fluctuating status indicates underlying communication problems that require attention. A replica in a Not Synchronizing state cannot guarantee data consistency or immediate failover readiness.

SQL Server Error Log Indications¶

The SQL Server error logs are invaluable diagnostic resources for pinpointing connection issues. When a connection timeout occurs, you will typically find specific error messages logged on both the primary and secondary replicas, providing crucial context. These messages clearly indicate a disruption in communication between the Availability Group partners.

For instance, the primary replica’s error log might show messages like:

2023-02-15 07:10:55.500 spid43s Always On availability groups connection with secondary database terminated for primary database 'agdb' on the availability replica 'SQL19AGN2' with Replica ID: {<replicaid>}. This is an informational message only. No user action is required.

Conversely, the secondary replica’s error log might report:

2023-02-15 07:11:03.100 spid31s A connection time-out has occurred on a previously established connection to availability replica 'SQL19AGN1' with id [<replicaid>]. Either a networking or a firewall issue exists or the availability replica has transitioned to the resolving role.

2023-02-15 07:11:03.100 spid31s Always On Availability Groups connection with primary database terminated for secondary database 'agdb' on the availability replica 'SQL19AGN1' with Replica ID: {<replicaid>}. This is an informational message only. No user action is required.

These log entries are critical timestamps that help correlate connection issues with other events in the system. They clearly indicate which replica observed the timeout and the partner it was attempting to communicate with.

Impaired Failover Readiness¶

For Availability Groups configured for automatic failover, intermittent connection problems can severely compromise this capability. If a synchronous-commit failover partner is repeatedly disconnected from the primary, an automatic failover attempt might be unsuccessful. This could lead to extended downtime during an actual outage, defeating the purpose of high availability. The system may not detect a healthy secondary replica to take over the primary role.

Querying Failover Readiness with DMVs¶

To assess the current failover readiness of your Availability Group databases, you can query the sys.dm_hadr_database_replica_cluster_states dynamic management view (DMV). This DMV provides insights into the state of each database within the Availability Group, including whether it is currently ready for failover. Regular monitoring of this DMV is a proactive measure against failover failures.

Consider the following SQL query to retrieve essential information about replica states:

SELECT drcs.database_name, drcs.is_failover_ready, ar.replica_server_name, ars.role_desc, ars.connected_state_desc,
ars.last_connect_error_description, ars.last_connect_error_number, ar.endpoint_url
FROM sys.dm_hadr_availability_replica_states ars JOIN sys.availability_replicas ar ON ars.replica_id=ar.replica_id
JOIN sys.dm_hadr_database_replica_cluster_states drcs ON ar.replica_id=drcs.replica_id
WHERE ars.role_desc='SECONDARY'

The is_failover_ready column is particularly important here, as a value of 0 would indicate that the database is not ready for an immediate failover. Such a state during an actual event would result in manual intervention and potential data loss or extended recovery times.

Deciphering Connection Timeout Error Messages¶

Understanding the meaning behind the various connection timeout error messages is fundamental to effective troubleshooting. SQL Server provides specific messages that differentiate between timeouts on new connections versus those on already established connections. Each message offers a hint about the underlying cause.

The SESSION_TIMEOUT Parameter¶

At the heart of Availability Group connection monitoring is the SESSION_TIMEOUT parameter. By default, this setting is configured for 10 seconds for each replica in an Availability Group. This value dictates how long a replica will wait to receive a response from its partner before it declares a connection timeout. If no response is received within this window, a timeout is reported in the SQL Server error log and the Windows Application log. Following a timeout, the replica immediately attempts to reconnect, retrying every five seconds until successful.

Common Timeout Messages (35201, 35206)¶

SQL Server distinguishes between timeouts that occur during the establishment of a new connection and those that occur on an existing one. These messages provide crucial context for diagnosis. Message 35201 indicates a problem establishing a new connection, often pointing to network configuration or endpoint issues.

Message 35206, on the other hand, signals a timeout on a previously established connection. This usually suggests a transient network interruption, a firewall issue, or that the partner replica has transitioned into a resolving role. Both messages clearly name the problematic replica and provide a unique ID for identification.

Message 35206 A connection timeout has occurred on a previously established connection to availability replica '<replicaname>' with id [<replicaid>]. Either a networking or a firewall issue exists or the availability replica has transitioned to the resolving role.

Message 35201 A connection timeout has occurred while attempting to establish a connection to availability replica '<replicaname>' with id [<replicaid>]. Either a networking or firewall issue exists, or the endpoint address provided for the replica is not the database mirroring endpoint of the host server instance.

These messages are critical starting points for investigation, directing your focus towards either initial handshake problems or ongoing communication stability.

Connection Termination Messages (35267)¶

In some scenarios, the partner replica might not detect a direct timeout but will instead report a connection loss to its associated Availability Group databases. This is often seen as message 35267. While not a direct “timeout” message, it signifies that the connection to the primary/secondary database has been terminated. This informational message typically follows a timeout event reported by the other replica.

Message 35267 Always On Availability Groups connection with primary/secondary database terminated for primary/secondary database '<databasename>' on the availability replica '<replicaname>' with Replica ID: {<replicaid>}. This is an informational message only. No user action is required.

This message confirms the impact of the communication breakdown on the individual availability databases within the group. It is a secondary indicator that corroborates the primary timeout messages.

Example: Endpoint Stoppage Scenario¶

To illustrate, consider a situation where the mirroring endpoint on the primary replica is intentionally stopped. The secondary replica, unable to communicate, will detect a connection timeout. Its error log will then report messages 35206 and 35267, indicating both the timeout on the established connection and the subsequent termination of the database connection.

Meanwhile, the primary replica, which initiated the endpoint stoppage, will not detect a timeout itself because it is still technically capable of communicating, even if its own endpoint is down. It will, however, report message 35267 for each of its availability databases, acknowledging the connection loss to the secondary. This example highlights how messages can appear differently on each side of a disconnected pair.

Root Causes of Replica Connection Timeouts¶

Intermittent connection timeouts are typically attributable to two main categories: issues within the SQL Server application itself or problems within the underlying network infrastructure. Diagnosing these requires a distinct approach for each category. Pinpointing the exact cause is often the most challenging part of the troubleshooting process.

Application-Related Issues within SQL Server¶

SQL Server, when under significant strain, might become too busy to service the mirroring endpoint connection within the configured SESSION_TIMEOUT period. This internal contention can lead to the replica appearing unresponsive to its partner, triggering a timeout. Such issues are usually indicative of performance bottlenecks within the SQL Server instance.

High CPU Utilization¶

One common cause is sustained 100 percent CPU utilization on the SQL Server host. This can be driven by intensive SQL Server workloads, such as complex queries, large batch jobs, or high transaction volumes. Alternatively, other applications running on the same server might consume excessive CPU resources, starving SQL Server. When the CPU is fully saturated, SQL Server threads, including those responsible for Availability Group communication, may not get sufficient processing time to respond to their partners, leading to timeouts.

Non-Yielding Scheduler Events¶

SQL Server relies on a cooperative scheduling mechanism where threads are expected to voluntarily yield the CPU to other threads after completing their work or waiting for a resource. A non-yielding scheduler event occurs when a SQL Server thread holds onto the CPU for an extended period without yielding. This can starve other critical SQL Server processes, including the mirroring endpoint communication, causing significant delays and ultimately leading to connection timeouts. These events are often symptoms of deep-seated performance problems or bugs.

Worker Thread Exhaustion and Memory Pressures¶

Other application-related issues include worker thread exhaustion or out-of-memory conditions within SQL Server. If the SQL Server instance runs out of available worker threads or experiences severe memory pressure, it may struggle to process incoming requests, including those from the mirroring endpoint. This inability to allocate resources or process tasks in a timely manner directly impacts its capacity to service the Availability Group connection, resulting in observed timeouts. Such issues often require detailed performance monitoring and configuration adjustments.

Network-Related Issues¶

When SQL Server itself appears healthy, the focus shifts to the network infrastructure connecting the Availability Group replicas. Network issues are a frequent culprit for intermittent connection timeouts. These problems are often invisible to the SQL Server application layer and require specialized network diagnostic tools.

Network Latency and Packet Loss¶

Network latency refers to the delay in data transmission between the primary and secondary replicas. If this latency exceeds the SESSION_TIMEOUT period, even healthy SQL Server instances will report a connection timeout. Similarly, packet loss, where data packets fail to reach their destination, can prevent the timely exchange of heartbeat signals and synchronization messages. Both high latency and packet loss can be caused by congested networks, faulty network hardware, misconfigured firewalls, or routing issues. Diagnosing these requires comprehensive network tracing to capture the communication between replicas during the timeout events.

Diagnosing Replica Connection Timeouts¶

Effective diagnosis of replica connection timeouts requires a systematic approach, combining SQL Server internal diagnostics with network-level tracing. Starting with an analysis of logs and historical data helps narrow down the potential causes. Gathering data from multiple sources is key to forming a complete picture.

Strategic Assessment: Timing and Location¶

Begin your investigation by thoroughly reviewing the history, frequency, and trends of the connection timeouts. Examine the SQL Server error logs to identify when and where the timeouts are being reported. Are they consistently occurring on the primary or a specific secondary replica? Do these errors cluster around certain times of the day, days of the week, or specific weeks of the month?

It’s also crucial to correlate these events with other scheduled activities, such as maintenance windows, batch processing jobs, or resource-intensive backups. Often, intermittent timeouts coincide with periods of peak workload or specific administrative tasks. This initial assessment can provide invaluable clues, helping you scope the problem and identify potential culprits.

Leveraging the AlwaysOn_health Extended Event Session¶

The AlwaysOn_health extended event session is a powerful diagnostic tool built into SQL Server. This session has been enhanced over time to include events specifically relevant to Availability Group connection issues, such as the ucs_connection_setup event. This event is triggered when a replica attempts to establish a connection with its partner, making it highly useful for troubleshooting connection timeout scenarios.

Extended events provide granular details about internal SQL Server operations. By reviewing the data captured by AlwaysOn_health, you can often see the exact sequence of events leading up to a connection timeout. This can reveal if the connection attempt failed, if it was unusually slow, or if a previously established connection was abruptly terminated.

Interpreting Extended Event Data¶

To effectively use the AlwaysOn_health data, you’ll need to open and analyze the .xel files using SQL Server Management Studio (SSMS). These files contain a wealth of information that can indicate exactly what was happening at the moment of the timeout. Look for ucs_connection_setup events that show failures or long durations, as well as any other events that indicate resource contention or internal processing delays. The event data can often point directly to the replica that initiated the connection, the partner it was trying to reach, and any associated error codes or states.

Querying Always On Distributed Management Views (DMVs)¶

Always On Distributed Management Views (DMVs) provide real-time information about the state and health of your Availability Groups. While they offer a snapshot, repeatedly querying these DMVs can help capture intermittent issues if the timing is right. Specifically, sys.dm_hadr_availability_replica_states is essential for understanding the connected state of each replica.

Understanding Connected States and Errors¶

The DMV sys.dm_hadr_availability_replica_states exposes critical columns such as connected_state_desc, last_connect_error_description, and last_connect_error_number. These columns indicate whether a replica is currently connected to its partner, and if not, the reason for the last connection failure. While an intermittent issue might not always be caught in a single query, frequent polling or combining DMV data with other diagnostic methods can prove effective.

SELECT ar.replica_server_name, ars.role_desc, ars.connected_state_desc,
ars.last_connect_error_description, ars.last_connect_error_number, ar.endpoint_url
FROM sys.dm_hadr_availability_replica_states ars JOIN sys.availability_replicas ar ON ars.replica_id=ar.replica_id

This query provides a concise overview of the connection status for all replicas, helping you quickly identify any current disconnections or previous connection errors.

Example DMV Output¶

If, for instance, the mirroring endpoint on the primary replica is stopped, querying this DMV from the secondary replica would show a disconnected state and details about the connection error. Conversely, querying from the primary replica would show its own state and the perceived state of all secondary replicas. This dual perspective is crucial for understanding which side is reporting the issue and why.

For example, a sustained disconnected state on a secondary might look something like this from the primary’s perspective, if the endpoint on the primary was disabled:

replica_server_name	role_desc	connected_state_desc	last_connect_error_description	last_connect_error_number	endpoint_url
SQL19AGN1	SECONDARY	DISCONNECTED	A connection timeout has occurred…	35206	TCP://SQL19AGN1.domain:5022
SQL19AGN2	PRIMARY	CONNECTED	NULL	0	TCP://SQL19AGN2.domain:5022

Detecting Non-Yielding Scheduler Events¶

Non-yielding scheduler events are a common internal SQL Server problem that can directly cause Availability Group connection timeouts. These events occur when a SQL Server worker thread does not release the CPU, starving other critical processes. SQL Server tracks these events, making them detectable through specific diagnostic tools.

The Role of sp_server_diagnostics¶

The sp_server_diagnostics stored procedure is an excellent tool for monitoring the health of a SQL Server instance. It includes a query_processing component that reports various metrics, including TrackingNonYieldingScheduler. This data point will show a non-zero hexadecimal value if a non-yielding scheduler event has been detected, even for durations as short as 5 to 10 seconds. This makes it highly relevant for identifying transient internal contention that could lead to timeouts.

Automating sp_server_diagnostics Data Collection¶

Because non-yielding events can be intermittent, collecting sp_server_diagnostics output continuously is more effective than manual execution. A SQL Agent job can be configured to run sp_server_diagnostics at a frequent interval, such as every five seconds, and append its output to a log file. It is recommended to set up this job on the partner replica – the server that is not reporting the connection timeout in its error log – to catch any internal issues on the communicating side.

Here’s an example batch file to create and start such a SQL Agent job:

USE [msdb]
GO
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Run sp_server_diagnostics',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
/****** Object: Step [Run SP_SERVER_DIAGNOSTICS] Script Date: 2/15/2023 4:20:41 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Run SP_SERVER_DIAGNOSTICS',
@subsystem=N'TSQL',
@command=N'sp_server_diagnostics 5',
@database_name=N'master',
@output_file_name=N'D:\cases\2423\sp_server_diagnostics_output.out',
@flags=2
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
EXEC sp_start_job 'Run sp_server_diagnostics'

Remember to adjust the @output_file_name to a valid path on your server. This script creates a job that continuously runs sp_server_diagnostics 5, effectively collecting data every five seconds without needing further scheduling.

Analyzing Diagnostic Output for Non-Yielding Events¶

Once a connection timeout is reported in the SQL Server error logs, immediately check the collected sp_server_diagnostics output file. Look for entries around the exact timestamp of the timeout event. Specifically, examine the TrackingNonYieldingScheduler data point within the query_processing component. If this value shows a non-zero hexadecimal value (e.g., 0x1 or similar) around the timeout time, it strongly indicates that a non-yielding scheduler event on that server contributed to the connection timeout.

This correlation provides direct evidence that internal SQL Server contention prevented timely communication. For instance, if SQL19AGN1 reports a timeout at 07:24:31, and the sp_server_diagnostics output from its partner SQL19AGN2 shows TrackingNonYieldingScheduler as active at 07:24:33, this points to SQL19AGN2 being unresponsive due to an internal scheduler issue.

Deep Dive into Non-Yielding Scheduler Investigations¶

If a non-yielding event is identified as the cause, further investigation is warranted. This involves:
1. Identifying Workloads: Determine which SQL Server workloads were active and consuming resources at the time of the non-yielding event. This might involve examining query history, SQL Agent job logs, or performance counter data.
2. Trend Analysis: Look for recurring patterns in these events – do they happen during specific operations or at particular times?
3. Performance Monitor Tracing: Collect detailed performance monitor traces on the affected system. Focus on key counters such as Processor::% Processor Time, Memory::Available MBytes, Logical Disk::Avg Disk Queue Length, and Logical Disk::Avg Disk sec/Transfer to assess system resource utilization comprehensively.
4. Support Escalation: If the root cause remains elusive, consider opening a SQL Server support incident. Provide all collected logs and traces for expert analysis, as non-yielding events can sometimes indicate complex internal issues.

Advanced Network Trace Collection¶

If SQL Server internal diagnostics do not pinpoint a clear application-related cause, the next step is to investigate the network. Network tracing provides a granular view of all network traffic between the replicas, allowing you to identify latency, packet loss, or firewall issues. This is a highly effective, albeit complex, method for diagnosing network-related timeouts.

Initiating Network Tracing¶

Start a Windows netsh network trace on both the primary and secondary replicas where connection timeouts are reported. This captures all network traffic, which can then be analyzed for anomalies. Use the following command at an administrative command prompt:

netsh trace start capture=yes persistent=yes overwrite=yes maxsize=500 tracefile=f:\trace.etl

Ensure that the tracefile path is valid and has sufficient disk space. Running netsh with persistent=yes ensures the trace continues across reboots, while maxsize prevents the trace file from growing indefinitely.

Automating Trace Stoppage on Error Events¶

To ensure that the network trace captures the specific moment of the timeout without being overwritten, you can create Windows scheduled tasks. These tasks should be configured to trigger upon the occurrence of specific SQL Server connection error events (e.g., Event IDs 35206 or 35267) in the Application log. When triggered, the task executes a batch file to stop the netsh trace, preserving the relevant data.

Create these scheduled tasks from an administrative command line:

schtasks /Create /tn Event35206Task /tr F:\stoptrace.bat /SC ONEVENT /EC Application /MO *[System/EventID=35206] /f /RL HIGHEST
schtasks /Create /tn Event35267Task /tr F:\stoptrace.bat /SC ONEVENT /EC Application /MO *[System/EventID=35267] /f /RL HIGHEST

You’ll need to create a stoptrace.bat file at F:\ (or your chosen path) with the content:

netsh trace stop

Once the events have occurred and the traces are captured, remember to delete these ONEVENT tasks to avoid unnecessary trace captures in the future:

PS C:\Users\sqladmin> Schtasks /Delete /tn Event35206Task /F
PS C:\Users\sqladmin> Schtasks /Delete /tn Event35267Task /F

Post-Collection Network Analysis¶

Analyzing the captured .etl network trace files is a specialized skill, often requiring tools like Microsoft Network Monitor or Wireshark. The goal is to identify increased network latency, retransmissions, dropped packets, or communication blocks (e.g., by firewalls) that coincide with the reported SQL Server connection timeouts. If you lack the expertise to interpret these traces, it is highly recommended to engage the Microsoft SQL Server Support team. Provide them with the trace files along with all other collected SQL Server logs for a comprehensive root cause analysis.

Mitigating Intermittent Connection Timeouts¶

Once the root cause of the intermittent connection timeouts has been identified, specific mitigation strategies can be applied. While addressing the underlying problem is paramount, some adjustments can also help in the short term or when the cause is a transient, unavoidable network fluctuation.

Adjusting the SESSION_TIMEOUT Parameter¶

A common mitigation strategy, especially if network latency is occasionally high but not consistently problematic, is to adjust the Availability Group replica’s SESSION_TIMEOUT property. The default value of 10 seconds might be too aggressive for environments with slightly higher-than-average, but still acceptable, network latency. Incrementing this value can provide more tolerance for transient network delays.

This setting is configured per replica. You should adjust it for both the primary and any affected secondary replicas. For example, if the default of 10 seconds is causing issues, you might increase it to 15 or 20 seconds. It’s crucial not to set this value excessively high, as it could mask genuine connectivity problems, delaying detection of a severe outage.

ALTER AVAILABILITY GROUP [YourAvailabilityGroupName]
MODIFY REPLICA ON 'YourReplicaServerName' WITH (SESSION_TIMEOUT = 15);

Carefully monitor the Availability Group after making this change to ensure the timeouts are mitigated without introducing new issues. Gradual increases are recommended to find an optimal balance between responsiveness and tolerance.

Proactive Monitoring and Maintenance¶

Beyond specific fixes, a proactive approach to monitoring and maintenance is essential for preventing intermittent connection timeouts. Regularly reviewing SQL Server error logs, Always On dashboards, and performance counters for CPU, memory, and disk I/O can help detect early warning signs of contention. Implementing robust network monitoring tools can also alert you to underlying infrastructure issues before they impact Availability Group communication. Regular network health checks and performance baselining are also highly recommended.

Conclusion and Call to Action¶

Intermittent SQL Server Availability Group connection timeouts can be complex, stemming from a variety of application and network issues. By systematically diagnosing symptoms, understanding error messages, and utilizing tools like AlwaysOn_health extended events, DMVs, and sp_server_diagnostics, you can effectively identify the root cause. While some issues require deeper investigation, adjusting the SESSION_TIMEOUT parameter can offer immediate mitigation.

Maintaining a stable and highly available SQL Server environment requires vigilance and a structured approach to troubleshooting. If you’ve encountered similar issues or have additional tips, please share your experiences in the comments below! Your insights can help the broader community.