We have a number of SQL Servers with Always on Availability Groups in asynchronous mode between a primary and secondary server with Manual fail over.
I created a latency report utilizing the below query that collects the data every min on each server.
On one of our servers the secondary routinely shows that it has an earlier last_commit_time then the primary.
I verified the server times are the same on both servers.
Why might this be?
;WITH
AG_Stats AS
(
SELECT AR.replica_server_name,
AG.name as AGName,
HARS.role_desc,
Db_name(DRS.database_id) [DBName],
DRS.last_commit_time
FROM sys.dm_hadr_database_replica_states DRS
INNER JOIN sys.availability_replicas AR ON DRS.replica_id = AR.replica_id
INNER JOIN sys.dm_hadr_availability_replica_states HARS ON AR.group_id = HARS.group_id
AND AR.replica_id = HARS.replica_id
INNER JOIN [sys].[availability_groups] AG on AG.group_id = AR.group_id
),
Pri_CommitTime AS
(
SELECT replica_server_name
, AGNAME
, DBName
, last_commit_time
FROM AG_Stats
WHERE role_desc = 'PRIMARY'
),
Sec_CommitTime AS
(
SELECT replica_server_name
, AGNAME
, DBName
, last_commit_time
FROM AG_Stats
WHERE role_desc = 'SECONDARY'
)
SELECT p.replica_server_name [primary_replica]
, p.AGNAME
, p.[DBName] AS [DatabaseName]
, s.replica_server_name [secondary_replica]
, DATEDIFF(ss,s.last_commit_time,p.last_commit_time) AS [Sync_Latency_Secs]
FROM Pri_CommitTime p
LEFT JOIN Sec_CommitTime s ON [s].[DBName] = [p].[DBName] and s.AGNAME = p.AGNAME
the secondary routinely shows that it has an earlier last_commit_time then the primary
, you mean if the time is say 1pm, the primary shows 1pm and the secondary shows 12:55pm, correct? – John Eisbrener Dec 10 '19 at 15:00