SELECT * FROM sys.dm_hadr_database_replica_states
DECLARE @ROWCOUNT Int SET @ROWCOUNT = ( SELECT COUNT (*) FROM sys.databases AS DB WITH (NOLOCK) INNER JOIN sys.dm_hadr_database_replica_states AS R WITH (NOLOCK) ON DB.database_id = R.database_ID WHERE R.is_primary_replica = 1 AND (DB.state <> 0) OR (R.database_state_desc <> 'ONLINE') ) IF @ROWCOUNT > 0 BEGIN RAISERROR('PRIMARY REPLICA DATABASE PROBLEMS EXIST' , 16, 1) END
Data-movement state, one of the following values.
0 = Not synchronizing. For a primary database, indicates that the database is not ready to synchronize its transaction log with the corresponding secondary databases. For a secondary database, indicates that the database has not started log synchronization because of a connection issue, is being suspended, or is going through transition states during startup or a role switch.
1 = Synchronizing. For a primary database, indicates that the database is ready to accept a scan request from a secondary database. For a secondary database, indicates that active data movement is occurring for the database.
2 = Synchronized. A primary database shows SYNCHRONIZED in place of SYNCHRONIZING. A synchronous-commit secondary database shows synchronized when the local cache says the database is failover ready and is synchronizing.
3 = Reverting. Indicates the phase in the undo process when a secondary database is actively getting pages from the primary database.
Caution: When a database on a secondary replica is in the REVERTING state, forcing failover to the secondary replica leaves the database in a state in which it cannot be started as a primary database. Either the database will need to reconnect as a secondary database, or you will need to apply new log records from a log backup.
4 = Initializing. Indicates the phase of undo when the transaction log required for a secondary database to catch up to the undo LSN is being shipped and hardened on a secondary replica.
Caution: When a database on a secondary replica is in the INITIALIZING state, forcing failover to the secondary replica leaves the database in a state in which it cannot be started as a primary database. Either the database will need to reconnect as a secondary database, or you will need to apply new log records from a log backup.
SYNCHRONIZED = data synchronized
SYNCHRONIZING = active data movement is currently occurring
Note: “Synchronizing” state is the normal, healthy state for an Asynchronous-commit replica. Asynchronous-commit will never be in Synchronized state.
Amount of log records of the primary database that has not been sent to the secondary databases, in kilobytes (KB).
Amount of log records in the log files of the secondary replica that has not yet been redone, in kilobytes (KB).