November 5, 2018 at 1:10 pm
Hi All,
I am hoping some one can help me as I am nto quite sure hwo to go about it. I got this query which gives me always on sync delay between primary and secondary node.
;WITH AG_Stats AS (SELECT AR.replica_server_name, HARS.role_desc, Db_name(DRS.database_id)[DBName], DRS.last_commit_timeFROM sys.dm_hadr_database_replica_states DRS INNER JOINsys.availability_replicas AR ON DRS.replica_id = AR.replica_id INNER JOINsys.dm_hadr_availability_replica_states HARS ON AR.group_id= HARS.group_id ANDAR.replica_id =HARS.replica_id ),Pri_CommitTime AS (SELECT replica_server_name, DBName, last_commit_timeFROM AG_StatsWHERE role_desc= 'PRIMARY'),Sec_CommitTime AS (SELECT replica_server_name, DBName, last_commit_timeFROM AG_StatsWHERE role_desc= 'SECONDARY')SELECT p.replica_server_name[primary_replica], p.[DBName]AS [DatabaseName], s.replica_server_name [secondary_replica], DATEDIFF(ss,s.last_commit_time,p.last_commit_time)AS [Sync_Lag_Secs]FROM Pri_CommitTimepLEFT JOIN Sec_CommitTime s ON .[DBName]= [p].[DBName]
I want to make changes so when the sync_lag_secs go above x numbers i will get a email ( alert).
Can someone help me how to go about it?
November 6, 2018 at 7:26 am
BoobyB - Monday, November 5, 2018 1:10 PMHi All,I am hoping some one can help me as I am nto quite sure hwo to go about it. I got this query which gives me always on sync delay between primary and secondary node.
;WITH AG_Stats AS (SELECT AR.replica_server_name, HARS.role_desc, Db_name(DRS.database_id)[DBName], DRS.last_commit_timeFROM sys.dm_hadr_database_replica_states DRS INNER JOINsys.availability_replicas AR ON DRS.replica_id = AR.replica_id INNER JOINsys.dm_hadr_availability_replica_states HARS ON AR.group_id= HARS.group_id ANDAR.replica_id =HARS.replica_id ),Pri_CommitTime AS (SELECT replica_server_name, DBName, last_commit_timeFROM AG_StatsWHERE role_desc= 'PRIMARY'),Sec_CommitTime AS (SELECT replica_server_name, DBName, last_commit_timeFROM AG_StatsWHERE role_desc= 'SECONDARY')SELECT p.replica_server_name[primary_replica], p.[DBName]AS [DatabaseName], s.replica_server_name [secondary_replica], DATEDIFF(ss,s.last_commit_time,p.last_commit_time)AS [Sync_Lag_Secs]FROM Pri_CommitTimepLEFT JOIN Sec_CommitTime s ON
.[DBName]= [p].[DBName]I want to make changes so when the sync_lag_secs go above x numbers i will get a email ( alert).
Can someone help me how to go about it?
That query won't give you anything other than an error if you try to run it.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply