sys.dm_hadr_availability_group_states (Transact-SQL)

  • Article

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)