-
Notifications
You must be signed in to change notification settings - Fork 1.1k
Closed
Description
Is your feature request related to a problem? Please describe.
When checking health on a SQL Server with AGs, it's easy to miss that a replica is getting out of sync.
Describe the solution you'd like
Add a warning that replicas are out of sync by more than 60 seconds. Here's a query from ChatGPT that seems to work well:
SELECT
ag.name AS [AG Name],
ar.replica_server_name AS [Replica Name],
drs.database_name,
drs.redo_queue_size AS [Redo Queue Size (KB)],
drs.log_send_queue_size AS [Log Send Queue Size (KB)],
drs.log_send_rate AS [Log Send Rate (KB/sec)],
drs.redo_rate AS [Redo Rate (KB/sec)],
ps.last_commit_time AS [Primary Commit Time],
drs.last_commit_time AS [Replica Commit Time],
DATEDIFF(SECOND, drs.last_commit_time, ps.last_commit_time) AS [Seconds Behind]
FROM
sys.dm_hadr_database_replica_states AS drs
JOIN
sys.availability_replicas AS ar
ON drs.replica_id = ar.replica_id
JOIN
sys.availability_groups AS ag
ON ar.group_id = ag.group_id
JOIN
sys.dm_hadr_database_replica_states AS ps
ON drs.group_id = ps.group_id
AND drs.database_id = ps.database_id
AND ps.is_local = 1 -- primary
WHERE
drs.is_local = 0 -- secondary
ORDER BY
[Seconds Behind] DESC;
Are you ready to build the code for the feature?
Yep! I'm on it.