sys.dm_hadr_database_replica_cluster_states (Transact-SQL)

sys.dm_hadr_database_replica_cluster_states (Transact-SQL)


Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns a row containing information intended to provide you with insight into the health of the availability databases in the AlwaysOn availability groups in each AlwaysOn availability group on the Windows Server Failover Clustering (WSFC) cluster. Query sys.dm_hadr_database_replica_states to answer the following questions:

Applies to: SQL Server (SQL Server 2012 through current version).

Column name

Data type




Identifier of the availability replica within the availability group.



Identifier of the database within the availability group. This identifier is identical on every replica to which this database is joined.



Name of a database that belongs to the availability group.



Indicates whether the secondary database is synchronized with the corresponding primary database. one of:

0 = The database is not marked as synchronized in the cluster. The database is not ready for a failover.

1 = The database is marked as synchronized in the cluster. The database is ready for a failover.



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 acknowledgement from that secondary database to the SUSPEND message.

NULL = Unknown (no quorum)



Indicates whether the database on this availability replica has been joined to the availability group, one of:

0 = Database is not 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.)



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 will have the value to which 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. Note that each secondary replica will have either the MAX value or a lower value that the primary replica has told the secondary replica to go back to.



The AlwaysOn 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).  

Requires VIEW SERVER STATE permission on the server.

Community Additions

© 2015 Microsoft