Availability group counters, events and health-check queries
Replica counters, AlwaysOn_health events and health queries for WSFC, groups, replicas, databases and listeners.
These queries check the performance counters, the Always On extended events and the health of an availability group. The health queries on the cluster state only return data while the WSFC cluster has quorum.
Replica performance counters
select object_name,counter_name,instance_name,cntr_value
from sys.dm_os_performance_counters
where object_name like '%replica%'
and instance_name like '%AppDb%'
order by cntr_value desc
Failover events from the AlwaysOn_health session
This query reads the event file of the AlwaysOn_health extended events session and returns the replica state changes that reached PRIMARY_NORMAL, the most recent first.
DECLARE @FileName NVARCHAR(4000)
SELECT @FileName = target_data.value('(EventFileTarget/File/@name)[1]','nvarchar(4000)')
FROM (
SELECT
CAST(target_data AS XML) target_data
FROM sys.dm_xe_sessions s
JOIN sys.dm_xe_session_targets t
ON s.address = t.event_session_address
WHERE s.name = N'AlwaysOn_health'
) ft
SELECT
XEData.value('(event/@timestamp)[1]','datetime2(3)') AS event_timestamp,
XEData.value('(event/data[@name="previous_state"]/text)[1]', 'varchar(255)') AS previous_state,
XEData.value('(event/data[@name="current_state"]/text)[1]', 'varchar(255)') AS current_state,
XEData.value('(event/data[@name="availability_replica_name"]/value)[1]', 'varchar(255)') AS availability_replica_name,
XEData.value('(event/data[@name="availability_group_name"]/value)[1]', 'varchar(255)') AS availability_group_name
FROM (
SELECT CAST(event_data AS XML) XEData, *
FROM sys.fn_xe_file_target_read_file(@FileName, NULL, NULL, NULL)
WHERE object_name = 'availability_replica_state_change'
) event_data
WHERE XEData.value('(event/data[@name="current_state"]/text)[1]', 'varchar(255)') = 'PRIMARY_NORMAL'
ORDER BY event_timestamp DESC;
I/O and page errors from the AlwaysOn_health session
The same event file, filtered on the error_reported event for error numbers 823, 824 and 829.
DECLARE @FileName NVARCHAR(4000)
SELECT @FileName = target_data.value('(EventFileTarget/File/@name)[1]','nvarchar(4000)')
FROM (
SELECT
CAST(target_data AS XML) target_data
FROM sys.dm_xe_sessions s
JOIN sys.dm_xe_session_targets t
ON s.address = t.event_session_address
WHERE s.name = N'AlwaysOn_health'
) ft
SELECT
XEData.value('(event/@timestamp)[1]','datetime2(3)') AS event_timestamp,
XEData.value('(event/data[@name="error_number"]/value)[1]', 'int') AS error_number,
XEData.value('(event/data[@name="severity"]/value)[1]', 'int') AS severity,
XEData.value('(event/data[@name="message"]/value)[1]', 'varchar(max)') AS message
FROM (
SELECT CAST(event_data AS XML) XEData, *
FROM sys.fn_xe_file_target_read_file(@FileName, NULL, NULL, NULL)
WHERE object_name = 'error_reported'
) event_data
WHERE XEData.value('(event/data[@name="error_number"]/value)[1]', 'int') IN (823, 824, 829)
ORDER BY event_timestamp DESC;
Health check
WSFC cluster
These two queries work only when the WSFC cluster has quorum.
SELECT * FROM sys.dm_hadr_cluster
SELECT * FROM sys.dm_hadr_cluster_members
SELECT cluster_name, quorum_type_desc, quorum_state_desc
FROM sys.dm_hadr_cluster WITH (NOLOCK) OPTION (RECOMPILE);
Availability groups
SELECT ag.name agname, ags.*
FROM sys.dm_hadr_availability_group_states ags
INNER JOIN sys.availability_groups ag ON ag.group_id = ags.group_id
Replicas, from the WSFC point of view
SELECT ar.replica_server_name ,harc.*
FROM sys.dm_hadr_availability_replica_cluster_states harc
INNER JOIN sys.availability_replicas ar ON ar.replica_id = harc.replica_id
Replicas, from the instance
Run this on the primary replica. On a secondary it shows only the information of that instance.
SELECT * FROM sys.dm_hadr_availability_replica_states
Databases, from the WSFC point of view
SELECT * FROM sys.dm_hadr_database_replica_cluster_states
Databases, from the instance
Run this on the primary replica. On a secondary it shows only the information of that instance.
SELECT ag.name ag_name ,
ar.replica_server_name ,
adc.database_name ,
hdrs.database_state_desc ,
hdrs.synchronization_state_desc ,
hdrs.synchronization_health_desc ,
agl.dns_name ,
agl.port
-- ,*
FROM sys.dm_hadr_database_replica_states hdrs
LEFT JOIN sys.availability_groups ag ON hdrs.group_id =ag.group_id
LEFT JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id
AND ar.replica_id = hdrs.replica_id
LEFT JOIN sys.availability_databases_cluster adc ON adc.group_id = ag.group_id
AND adc.group_database_id = hdrs.group_database_id
LEFT JOIN sys.availability_group_listeners agl ON agl.group_id = ag.group_id
ORDER BY ag.name , adc.database_name
Listeners
SELECT agl.dns_name, agl.port, aglia.*
FROM
sys.availability_group_listener_ip_addresses aglia
INNER JOIN sys.availability_group_listeners agl
ON agl.listener_id = aglia.listener_id
Notes
- The
AppDbfilter in the first query is an example database name. Replace it with the database you want, or remove the filter. - The failover query reads the event files of the
AlwaysOn_healthsession. The join usest.event_session_address; the source had a placeholder in that position. - The
WITH (NOLOCK)hint in the quorum query is from the source. It can read uncommitted state. - The "-- ,*" line is a commented column list kept from the source.