Skip to content

sp_Blitz: add warning for AG replica more than 60 seconds behind #3635

@BrentOzar

Description

@BrentOzar

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.

Metadata

Metadata

Assignees

Type

No type

Projects

No projects

Relationships

None yet

Development

No branches or pull requests

Issue actions