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

synchronization_state

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

Queue

log_send_queue_size

Amount of log records of the primary database that has not been sent to the secondary databases, in kilobytes (KB).

redo_queue_size

Amount of log records in the log files of the secondary replica that has not yet been redone, in kilobytes (KB).

Sources:

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-hadr-database-replica-states-transact-sql?view=sql-server-ver15

Last modified: December 27, 2019

Author

Comments

Write a Reply or Comment