Επεξεργασία

Κοινή χρήση μέσω

sys.dm_hadr_database_replica_cluster_states (Transact-SQL)

  • Άρθρο

Applies to: SQL Server

Returns a row containing insight into the health of the availability databases in the Always On availability groups in each Always On availability group on the Windows Server Failover Clustering (WSFC) cluster. Query sys.dm_hadr_database_replica_cluster_states to answer the following questions:

  • Are all databases in an availability group ready for a failover?

  • After a forced failover, has a secondary database suspended itself locally and acknowledged its suspended state to the new primary replica?

  • If the primary replica is currently unavailable, which secondary replica would allow the minimum data loss if it becomes the primary replica?

  • When the value of the sys.databases log_reuse_wait_desc column is AVAILABILITY_REPLICA, which secondary replica in an availability group is holding up log truncation on a given primary database?

Column name Data type Description
replica_id uniqueidentifier Identifier of the availability replica within the availability group.
group_database_id uniqueidentifier Identifier of the database within the availability group. This identifier is identical on every replica to which this database is joined.
database_name sysname Name of a database that belongs to the availability group.
is_failover_ready bit Indicates whether the secondary database is synchronized with the corresponding primary database. one of:

0 = The database isn't marked as synchronized in the cluster. The database isn't ready for a failover.

1 = The database is marked as synchronized in the cluster. The database is ready for a failover.
is_pending_secondary_suspend bit Indicates whether, after a forced failover, the database is pending suspension, one of:

0 = Any states except for HADR_SYNCHRONIZED_SUSPENDED.

1 = HADR_SYNCHRONIZED_SUSPENDED. When a forced failover completes, each of the secondary databases is set to HADR_SYNCHONIZED_SUSPENDED and remains in this state until the new primary replica receives an acknowledgment from that secondary database to the SUSPEND message.

NULL = Unknown (no quorum)
is_database_joined bit Indicates whether the database on this availability replica has been joined to the availability group, one of:

0 = Database isn't joined to the availability group on this availability replica.

1 = Database is joined to the availability group on this availability replica.

NULL = unknown (The availability replica lacks quorum.)
recovery_lsn numeric(25,0) On the primary replica, the end of the transaction log before the replica writes any new log records after recovery or failover. On the primary replica, the row for a given secondary database has the value that the primary replica needs the secondary replica to synchronize to (that is, to revert to and reinitialize to).

On secondary replicas, this value is NULL. Each secondary replica has either the MAX value or a lower value that the primary replica has told the secondary replica to go back to.
truncation_lsn numeric(25,0) The Always On availability groups log truncation value, which may be higher than the local truncation LSN if local log truncation is blocked (such as by a backup operation).

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