What’s the issue?
Each replica in an Always On Availability Group has a current operational state, including values such as ONLINE, OFFLINE, PRIMARY, SECONDARY, RESOLVING, and various synchronization states. SQL Server records state transitions in the AlwaysOn_health Extended Events session and in the SQL Server error log, providing a history of when and how each replica’s state has changed.
This finding identifies AGs where one or more replicas have undergone a state change within the last 24 hours. The check is based on entries in the AlwaysOn_health session and the error log that record role changes, synchronization state changes, and connectivity events.
A recent state change is not necessarily a problem, but it warrants review to confirm whether the change was expected or whether it indicates an underlying issue that needs attention.
Why is this a problem?
A state change can result from several different events, each with different implications. A planned failover, performed for maintenance or to test recovery procedures, is a normal operation and the resulting state change is expected. An unplanned failover triggered by health detection or connectivity loss indicates that something disrupted the AG, which deserves investigation even if the AG recovered automatically.
Brief connectivity issues between replicas can produce state changes without an actual failover, particularly when session timeouts are short or when the network is intermittently unstable. Repeated state changes within a short window often indicate ongoing network instability, virtualization layer issues such as host live migration, or a partner replica that is struggling with its own resource pressure.
State changes also affect application connectivity. Applications connected through the AG listener may experience reconnection attempts, transaction rollbacks, or temporary failures during the change, even when the resulting state is healthy. Repeated state changes can cause user visible disruption that is not obvious from the AG dashboard once the AG has stabilized.
What should you do about this?
Review the recent state changes of the availability group using sp_CheckAG @Mode = 2, or by manually reviewing the AlwaysOn_health Extended Events session, the SQL Server error log on each replica, and the Windows event log for the relevant time window. Look for cluster events, network errors, lease timeouts, and the specific reason recorded for any role transitions.
Correlate the SQL Server side findings with infrastructure events from the same period, including network maintenance, virtualization host activity, storage events, and operating system updates. State changes that align with known infrastructure activity are usually expected, while unexplained state changes warrant deeper investigation.
If the state change was caused by network instability, work with the network team to identify and address the underlying cause. Consider raising the session timeout to reduce sensitivity to brief network events while the underlying issue is being investigated. Persistent network issues that affect AG stability should be treated as a blocker for the high availability strategy until resolved.
If the state change was caused by an unplanned failover, confirm that the AG returned to the desired state, that all replicas are synchronized, and that any monitoring or alerting tied to the previous primary has been adjusted. Investigate the cause of the failover and document the findings so the team builds a record of AG behavior and can identify trends over time.
Read more…
Configure Extended Events for availability groups – SQL Server Always On | Microsoft Learn