MSSQL AlwaysOn Diagnostics

Databases

SQL Server

MSSQL Availability

MSSQL AlwaysOn MFSP

Maximum Failures in Specified Period

Quick Check

SSMS

You can tell if a database is a read-only replica in SSMS because it has (Synchronized) appended to the database name in Object Explorer

Show Dashboard

T-SQL

SELECT * FROM sys.dm_hadr_availability_replica_states;

SELECT * FROM sys.dm_hadr_database_replica_states;

Check for...

synchronized state on synchronous replicas
healthy endpoints
No backlog in log send queue

Always On Health Events

hadr_trace_message

SELECT DATEADD(mi, -DATEDIFF(mi, GETUTCDATE(), GETDATE()), event_data.value('(event/@timestamp)[1]', 'datetime')) AS [Event Time], event_data.value('(event/data[@name="message"]/value)[1]', 'nvarchar(max)') AS [Trace Message] FROM (SELECT CAST(event_data AS XML) AS event_data FROM sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL) WHERE object_name = 'hadr_trace_message' ) AS tab ORDER BY [Event Time] DESC;

UpdateHadronTruncationLsn(7) (force=0): Primary: 0:0:0 Secondary: 368:260:1 Partial Quorum : 0:0:0 Aggregation: 368:260:1 Persistent: 368:255:2
Each part refers to a different view of the truncation LSN (Log Sequence Number):Primary: The LSN from the primary replica. 0:0:0 here may indicate no truncation point was reported or it's not applicable.Secondary: The LSN from the secondary replica(s). This is the highest LSN that the secondary has acknowledged.Partial Quorum: LSN based on a subset of replicas that form a quorum. Often 0:0:0 unless quorum is partially met.Aggregation: The aggregated LSN across all replicas, used to determine the safe truncation point.Persistent: The LSN that has been persisted as the truncation point in the system.
These messages started appearing more frequently in SQL Server 2019 CU4 and later, often every few minutes.They are informational and not indicative of a problem unless accompanied by errors.They can be suppressed using:DBCC TRACEON(3605, -1)Check with...DBCC TRACESTATUS(3605);To remove the suppression...DBCC TRACEOFF(3605, -1)

ucs_connection_setup

This event is triggered when SQL Server attempts to establish or re-establish a connection between replicas in an availability group.

DECLARE @TargetDate DATE = '2025-11-04'; -- Change this to your desired date
SELECT DATEADD(mi,-DATEDIFF(mi,GETUTCDATE(),GETDATE()),event_data.value('(event/@timestamp)[1]','datetime')) AS [Event Time],       event_data.value('(event/data[@name="address"]/value)[1]', 'nvarchar(max)') AS [Address],       event_data.value('(event/data[@name="circuit_id"]/value)[1]', 'nvarchar(max)') AS [Circuit ID],       event_data.value('(event/data[@name="connection_id"]/value)[1]', 'nvarchar(max)') AS [Connection ID],       event_data.value('(event/data[@name="endpoint_type"]/value)[1]', 'nvarchar(max)') AS [Endpoint Type],
       -- Endpoint Type Lookup       CASE event_data.value('(event/data[@name="endpoint_type"]/value)[1]', 'int')           WHEN 1 THEN 'Database Mirroring'           WHEN 2 THEN 'Service Broker'           WHEN 3 THEN 'Always On Availability Groups'           WHEN 4 THEN 'Cluster Endpoint'           ELSE 'Unknown'       END AS [Endpoint Type Desc],
       event_data.value('(event/data[@name="error_message"]/value)[1]', 'nvarchar(max)') AS [Error Message],       event_data.value('(event/data[@name="error_number"]/value)[1]', 'bigint') AS [Error Number],       event_data.value('(event/data[@name="obj_address"]/value)[1]', 'nvarchar(max)') AS [Object Address],       event_data.value('(event/data[@name="setup_event"]/value)[1]', 'nvarchar(max)') AS [Setup Event],
       -- Setup Event Lookup       CASE event_data.value('(event/data[@name="setup_event"]/value)[1]', 'int')           WHEN 0 THEN 'Initial Connection Setup'           WHEN 1 THEN 'Reconnection Attempt'           WHEN 2 THEN 'Connection Teardown'           WHEN 3 THEN 'Endpoint Negotiation'           WHEN 4 THEN 'Authentication Handshake'           WHEN 5 THEN 'Transport Initialization'           WHEN 6 THEN 'Stream Creation'           WHEN 7 THEN 'Stream Teardown'           WHEN 8 THEN 'Circuit Creation'           WHEN 9 THEN 'Circuit Teardown'           ELSE 'Unknown'       END AS [Setup Event Desc],
       event_data.value('(event/data[@name="stream_status"]/value)[1]', 'nvarchar(max)') AS [Stream Status],
       -- Stream Status Lookup       CASE event_data.value('(event/data[@name="stream_status"]/value)[1]', 'int')           WHEN 0 THEN 'Not Started'           WHEN 1 THEN 'Active'           WHEN 2 THEN 'Closed'           WHEN 3 THEN 'Failed'           WHEN 4 THEN 'Retrying'           ELSE 'Unknown'       END AS [Stream Status Dec]
  FROM (SELECT CAST(event_data AS XML) AS event_data          FROM sys.fn_xe_file_target_read_file('AlwaysOn_health*.xel', NULL, NULL, NULL)         WHERE object_name = 'ucs_connection_setup'       ) AS tab WHERE DATEADD(mi,-DATEDIFF(mi,GETUTCDATE(),GETDATE()),event_data.value('(event/@timestamp)[1]','datetime'))           BETWEEN @TargetDate AND DATEADD(DAY, 1, @TargetDate) ORDER BY [Event Time] DESC;

A connection is the basic TCP-level link between two endpoints (e.g., between primary and secondary replicas).It represents the network socket used for communication.

Circuit ├─ Connection 1 │ ├─ Stream A │ └─ Stream B └─ Connection 2 ├─ Stream C └─ Stream D

A stream is a logical data flow over a connection.It handles message framing, ordering, and delivery guarantees.Multiple streams can exist over a single connection, similar to how HTTP/2 multiplexes requests.In Availability Groups, streams are used to transfer log blocks, metadata, and control messages.

A circuit is a higher-level abstraction that manages the lifecycle of communication between replicas.It coordinates setup, teardown, failover, and retries.Circuits encapsulate one or more streams and connections, and are responsible for resilience and state management.

Cluster Log

Get-ClusterLog -Node MYSERVER -TimeSpan 15

Causes Powershell to generate Cluster.log for MYSERVER covering the last 15 minutes

Mode LastWriteTime Length Name---- ------------- ------ -----a---- 1/2/2025 1:31 PM 413460 Cluster.log

Open Cluster.log in Notepad.

Useful search terms can be things like...

failoverCount
Process has
=== Nodes ===
permission
Connect to SQL Server

Check

USE MASTER

GO

WHILE 1=1

BEGIN

PRINT CONVERT(VARCHAR(20), GETDATE(),120)

DECLARE @max INT;

SELECT @max = max_workers_count

FROM sys.dm_os_sys_info;

SELECT GETDATE() AS 'CurrentDate',

@max AS 'TotalThreads',

SUM(active_Workers_count) AS 'CurrentThreads',

@max - SUM(active_Workers_count) AS 'AvailableThreads',

SUM(runnable_tasks_count) AS 'WorkersWaitingForCpu',

SUM(work_queue_count) AS 'RequestWaitingForThreads'

--SUM(current_workers_count) AS 'AssociatedWorkers'

FROM sys.dm_os_Schedulers

WHERE STATUS = 'VISIBLE ONLINE';

WAIT FOR DELAY '0:0:15'

END

Quorum Model

To view the quorum model...

SELECT cluster_name,

quorum_type_desc,

quorum_state_desc

FROM sys.dm_hadr_cluster;

To view the node votes...

SELECT member_name,

number_of_quorum_votes

FROM sys.dm_hadr_cluster_members;

NT Authority\SYSTEM

This login account should have the following permssions.

(these are granted by default but may have been revoked)

Alter Any Availability Group
Connect SQL
View server state

Check Failover Readiness

SELECT database_name, is_failover_ready

FROM sys.dm_hadr_database_replica_cluster_states

WHERE replica_id IN (SELECT replica_id

FROM sys.dm_hadr_availability_replica_states)

Monitoring

Latency

TODO

Log Send Queue

TODO

Synchronization State

TODO

Failover Cluster Node Health

TODO

Disk I/O

TODO

Memory Pressure

TODO

Network Errors

TODO

Bibliography & References

https://www.sqlservercentral.com/search/always+on/https://www.seangallardy.com/olved-steries-1-sql-fci-failovers/https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2012/jj191711(v=msdn.10)https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/availability-groups/troubleshooting-automatic-failover-problems(1) https://learn.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/failover-policy-for-failover-cluster-instances

sys.dm_hadr_clustersys.dm_hadr_cluster_memberssys.dm_os_cluster_nodessys.dm_hadr_cluster_networks