LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon