sys.dm_hadr_availability_group_states (Transact-SQL)

Returns a row for each AlwaysOn 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.

Note

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

Column name	Data type	Description
group_id	uniqueidentifier	Unique identifier of the availability group.
primary_replica	varchar(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_health	tinyint	Indicates 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_desc	nvarchar(60)	Description of primary_replica_health, one of: ONLINE_IN_PROGRESS ONLINE NULL
secondary_recovery_health	tinyint	Indicates 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_desc	nvarchar(60)	Description of secondary_recovery_health, one of: ONLINE_IN_PROGRESS ONLINE NULL
synchronization_health	tinyint	Reflects a rollup of the synchronization_health of all availability replicas in the availability group, one of: Value Description 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_desc	nvarchar(60)	Description of synchronization_health, one of: NOT_HEALTHY PARTIALLY_HEALTHY HEALTHY

Security

Permissions

Requires VIEW SERVER STATE permission on the server.

See Also

Concepts

Monitor Availability Groups (Transact-SQL)

AlwaysOn Availability Groups (SQL Server)

Other Resources

AlwaysOn Availability Groups Dynamic Management Views and Functions (Transact-SQL)