sys.dm_hadr_availability_group_states (Transact-SQL)

 

Updated: June 10, 2016

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2012)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Returns a row for each Always On availability group that possesses an availability replica on the local instance of SQL Server. Each row displays the states that define the health of a given availability group.

System_CAPS_ICON_note.jpg Note


To obtain the complete list of, query the sys.availability_groups catalog view.

Column nameData typeDescription
group_iduniqueidentifierUnique identifier of the availability group.
primary_replicavarchar(128)Name of the server instance that is hosting the current primary replica.

NULL = Not the primary replica or unable to communicate with the WSFC failover cluster.
primary_recovery_healthtinyintIndicates the recovery health of the primary replica, one of:

0 = In progress

1 = Online

NULL

On secondary replicas the primary_recovery_health column is NULL.
primary_recovery_health_descnvarchar(60)Description of primary_replica_health, one of:

ONLINE_IN_PROGRESS

ONLINE

NULL
secondary_recovery_healthtinyintIndicates the recovery health of a secondary replica replica,one of:

0 = In progress

1 = Online

NULL

On the primary replica, the secondary_recovery_health column is NULL.
secondary_recovery_health_descnvarchar(60)Description of secondary_recovery_health, one of:

ONLINE_IN_PROGRESS

ONLINE

NULL
synchronization_healthtinyintReflects a rollup of the synchronization_health of all availability replicas in the availability group. Below are the possible values and their descriptions.

0: Not healthy. None of the availability replicas have a healthy synchronization_health (2 = HEALTHY).

1: Partially healthy. The synchronization health of some, but not all, availability replicas is healthy.

2: Healthy. The synchronization health of every availability replica is healthy.

For information about replica synchronization health, see the synchronization_health column in sys.dm_hadr_availability_replica_states (Transact-SQL).
synchronization_health_descnvarchar(60)Description of synchronization_health, one of:

NOT_HEALTHY

PARTIALLY_HEALTHY

HEALTHY

Permissions

Requires VIEW SERVER STATE permission on the server.

Monitor Availability Groups (Transact-SQL)
Always On Availability Groups (SQL Server)
Always On Availability Groups Dynamic Management Views and Functions (Transact-SQL)

Community Additions

ADD
Show: