Master of None

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’[email protected];” 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'[email protected];',
@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

Tiny URL for this post:
 Copy