Bewerken

Delen via


sys.dm_hadr_availability_group_states (Transact-SQL)

Applies to: SQL Server

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.

Note

To obtain the complete list of availability groups, 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 and 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, 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. The possible values and their descriptions are as follows:

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.
synchronization_health_desc nvarchar(60) Description of synchronization_health, one of:

NOT_HEALTHY

PARTIALLY_HEALTHY

HEALTHY

Permissions

For SQL Server 2019 (15.x) and earlier versions, requires VIEW SERVER STATE permission on the server.

For SQL Server 2022 (16.x) and later versions, requires VIEW SERVER PERFORMANCE STATE permission on the server.

See also