Blog Post

Check health and status of Availability Group using DMVs

,





-- Health and status of WSFC cluster. These two queries work only if the WSFC has quorum
SELECT * FROM sys.dm_hadr_cluster
SELECT * FROM sys.dm_hadr_cluster_members
-- Health of the AGs
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
-- Health and status of AG replics from the WsFC perspective
SELECT ar.replica_server_name,harc.* FROM sys.dm_hadr_availability_replica_cluster_states harc INNER JOINsys.availability_replicas ar ON ar.replica_id = harc.replica_id
-- Health and status of AG replicas, run this on the primary replica. 
-- On secondary this will only show info for that instance
SELECT * FROM sys.dm_hadr_availability_replica_states 
-- Health and status of AG databases from the WSFC perspective
SELECT * FROM sys.dm_hadr_database_replica_cluster_states 
-- Health and status of AG databases, run this on the primary replica. 
-- On secondary this will only show info for 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 ONhdrs.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
-- Health and status of AG listeners
SELECT agl.dns_name, agl.port, aglia.* FROM sys.availability_group_listener_ip_addresses aglia INNER JOINsys.availability_group_listeners agl ON agl.listener_id = aglia.listener_id

Rate

5 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (1)

You rated this post out of 5. Change rating