Why does dm_hadr_database_replica_states show a different AOAG LAG time than the GUI?
0
votes
1
answer
92
views
When I'm querying
But when I go to the AOAG dashboard and select the "Estimated Data Loss (time)" column, it shows 1 day.
Am I missing something here? Why the difference?
This is a query I'd like to use to create an alert if I knew the correct value:
SELECT
ag.name AS AvailabilityGroupName,
dr.name AS DatabaseName,
ars.replica_server_name AS ReplicaServerName,
drs.log_send_queue_size AS LogSendQueueSizeKB,
drs.redo_queue_size AS RedoQueueSizeKB,
drs.redo_rate AS RedoRateKBPerSec,
drs.secondary_lag_seconds
FROM
sys.availability_groups AS ag
JOIN
sys.availability_replicas AS ar ON ag.group_id = ar.group_id
JOIN
sys.dm_hadr_database_replica_states AS drs ON ar.replica_id = drs.replica_id
JOIN
sys.databases AS dr ON drs.database_id = dr.database_id
JOIN
sys.availability_replicas AS ars ON drs.replica_id = ars.replica_id
WHERE
drs.is_primary_replica = 0 -- Focus on secondary replicas
AND drs.secondary_lag_seconds > 3600
select * from sys.dm_hadr_database_replica_states
, I see 11k seconds (3 hours).

Asked by Racer SQL
(7562 rep)
Sep 3, 2025, 06:05 PM
Last activity: Sep 6, 2025, 02:57 PM
Last activity: Sep 6, 2025, 02:57 PM