Monitoring the synchronization status of SQL Availability Groups replicated databases, on a replica, is very straightforward.
Synchronization state & health
SELECT DB_NAME(database_id) as [name], [synchronization_state_desc], [synchronization_health_desc], [database_state_desc] FROM sys.dm_hadr_database_replica_states;
- synchronization_state: Data-movement state
- synchronization_health: Reflects the intersection of the synchronization state of a database that is joined to the availability group on the availability replica and the availability mode of the availability replica
- database_state: database state
Synchronization date / times
SELECT DB_NAME(database_id) as [name], [last_sent_time], [last_received_time], [last_commit_time] FROM sys.dm_hadr_database_replica_states;
|DB1||2022-06-15 11:47:34.317||2022-06-15 11:47:34.317||2022-06-15 11:05:26.687|
|DB2||2022-06-15 11:47:34.180||2022-06-15 11:47:34.180||2022-06-15 11:05:28.533|
|DB3||2022-06-15 11:47:35.020||2022-06-15 11:47:35.020||2022-06-15 11:47:34.827|
- last_sent_time: Time when the last log block was sent.
- last_received_time: Time when the log block ID in last message received was read on the secondary replica.
- last_commit_time: Time corresponding to the last commit record.
What worked for me?
SELECT DB_NAME(database_id) as [name], synchronization_health_desc, DATEDIFF(MILLISECOND,last_received_time,GETDATE()) as [last_received_ms], [last_commit_time] FROM sys.dm_hadr_database_replica_states;
A combination of the above queries:
- Database name
- Health of the entire availability group
- How many milliseconds has passed from the last message received on the replica
- Last commit time on the primary database
What about secondary_lag_seconds?
The number of seconds that the secondary replica is behind the primary replica during synchronization.
It’s a great measurement, but the value is
NULL on a repilca.