AlwaysOn Availability Groups – Query to Find Latency Part 3

Like Part 1 and Part 2, I use a similar query to find latency in AlwaysOn Availability Groups. The queries will return one line for each DB in the group. Part 1 , we looked at a simple query to grab the current status of the replica. Part 2, we looked at populating a Global Temp table every minute to gain a historical perspective of our AG latency. This query will look into creating an email notification when certain criteria is met. It will send the current status to a specific set of email recipients.

The example will be around specific criteria set as a step used in a SQL Agent job. If you use something like this, you need to tweak it to you organization’s needs. I do suggest that you set the job to a frequency that will not overflow your inbox with notices.

Send an email from server when latency for a specfic database datbase is over x number of minutes.

  •  Alter If statement > value for specific needs. This example fires of fthe email when latency is over 60 minutes.
  •  Alter “database_id = DB_ID(‘MyAGDatabase’)” to specify datbase to monitor
  • Alter “@recipients=N’DBA@mycompany.com;” to specify recipients. I suggest using a email distribution list over listing spefic emails.

The example is pretty simple and intended for demonstration purposes.

SET NOCOUNT ON 
GO
IF (SELECT CAST((redo_queue_size/redo_rate/60.0) as decimal(10,2))FROM sys.dm_hadr_database_replica_states WHERE database_id = DB_ID('MyAGDatabase') and last_redone_time is not null)  > 60
BEGIN

EXEC msdb.dbo.sp_send_dbmail
@recipients=N'DBA@mycompany.com;',
@subject = N'Availability Group Latency Alert',
@body = N'Availability Group Latency alert has been raised.',
@query=' SET NOCOUNT ON; SELECT  ''
Last Commit_Time:   ''+Convert(VARCHAR(20),last_commit_time,22) +''

Time Behind Primary:   ''+CAST(((DATEDIFF(s,last_commit_time,GetDate()))/3600) as varchar) + '' hour(s), ''

+ CAST((DATEDIFF(s,last_commit_time,GetDate())%3600)/60 as varchar) + ''min, ''

+ CAST((DATEDIFF(s,last_commit_time,GetDate())%60) as varchar) + '' sec 

Redo Queue Size (KB):   ''+CAST(redo_queue_size as VARCHAR(25))+''

Redo Rate (KB/sec):   ''+CAST(redo_rate as VARCHAR(25))+''

Estimated Completion Time:   ''+CONVERT(VARCHAR(20),DATEADD(mi,(redo_queue_size/redo_rate/60.0),GETDATE()),22)+''

Estimated Recovery Time Minutes:   ''+ CAST(CAST((redo_queue_size/redo_rate/60.0) as decimal(10,2)) as VARCHAR(20))+''

Estimated Recovery Time Seconds:   ''+ CAST((redo_queue_size/redo_rate) as VARCHAR(12))
FROM sys.dm_hadr_database_replica_states
WHERE database_id = DB_ID('MyAGDatabase') and last_redone_time is not null;',
@query_result_header = 0,
@importance = 'High'

END
Share the joy
  •  
  •  
  •  
  •  
  •  
  •  
  •  

Tiny URL for this post:
 

Leave a Comment


NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

WordPress Anti Spam by WP-SpamShield