Make SQL Server Agent Alert Notifications Smarter and More Flexible

By: Jeffrey Yao | Comments (2) | Related: > SQL Server Agent

Problem

SQL Server Agent alerts are a convenient way for monitoring many events of interest. We usually set up a job to send emails once an alert is triggered. However, there are two problems with alerts:

The same error may occur multiple times in a short period of time, like within one minute, we may receive numerous alert emails, i.e. email flooding.
There is no way to add sophisticated control to how alert emails will respond to the frequency or density of the alerts. For example, in the first [M] minutes, I want to get only the first [N] emails? Or at [N+1] alert, the alert email will become high priority and cc’ed to high level support staff? Or, I only want to get the 2^nd alert, because there is usually a system hiccup that may cause an alert once.

Can we create a better solution to address these two issues?

Solution

To be fair, SQL Server has a simple mechanism to avoid alert flooding, which can be adjusted in the alert property window as shown below.

We can define the delay time between responses to ensure that within the delayed time, we only get one response.

This should be good for most business cases, but for more sophisticated or niche requirements, such as, I want to allow three alerts instead of just one alert, then this internal solution may not work.

Solution Algorithm

We assume an alert will trigger a job to do the notification instead of notifying an operator directly, because inside a job we can enforce more logic. The main logic in the algorithm is as follows:

Inside the job, before sending a notification, check the job execution history using the agent token (JOBID)
Count the jobs successful execution time since a specific time point (such as [M] minute ago)
Based on the execution count, decide the notification method, i.e. mark the priority of the email or send to next level of stakeholders.
Add more detailed information about the alert history, such as "The alert has been fired [X] times since [hh:mm:ss]", "The last alert occurred at [hh:mm:ss]", etc.

Implementing Smarter SQL Server Alerts

We will first create an alert and its responding job. We assume the alert will respond to an error number 50001, and the responding job simply sends a notification email.

We first create a user defined error message.

-- create a user defined message with error number 50001
USE master;  
GO  
EXEC sp_addmessage @msgnum = 50001, @severity = 15,   
   @msgtext = N'This is a test message',   
   @lang = 'us_english';

We then create a response job, we simplify it to contain only one job step, i.e. send an database mail (we assume the database mail is configured / set up on the sql server instance).

The job step contains the following code:

USE [msdb]
GO

if exists (select * from dbo.sysjobs where name = 'Alert Response Job')
   exec msdb.dbo.sp_delete_job @job_name= N'Alert Response Job';

DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name=N'Alert Response Job', 
      @enabled=1, 
      @notify_level_eventlog=0, 
      @notify_level_email=0, 
      @notify_level_netsend=0, 
      @notify_level_page=0, 
      @delete_level=0, 
      @category_name=N'[Uncategorized (Local)]', 
      @owner_login_name=N'sa', @job_id = @jobId OUTPUT

EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'S1', 
      @step_id=1, 
      @cmdexec_success_code=0, 
      @on_success_action=1, 
      @on_success_step_id=0, 
      @on_fail_action=2, 
      @on_fail_step_id=0, 
      @retry_attempts=0, 
      @retry_interval=0, 
      @os_run_priority=0, @subsystem=N'TSQL', 
      @command=N'declare @subject nvarchar(300);
set @subject = ''alert response at '' + convert(varchar, getdate(), 8);
exec sp_send_dbmail @recipients=''[email protected]'', @subject=@subject, @body=''hello world'';',  --change email acct to your own
      @database_name=N'msdb', 
      @flags=0;
EXEC msdb.dbo.sp_add_jobserver @job_name='Alert Response Job', @server_name = @@servername;

We finally create an alert specific for this error number 50001 as follows, and the response is to call a job.

The alert-generating code is as follows:

USE [msdb]

EXEC msdb.dbo.sp_add_alert @name=N'MyAlert', 
      @message_id=50001, 
      @severity=0, 
      @enabled=1, 
      @delay_between_responses=0, 
      @include_event_description_in=1, 
      @notification_message=N'MyAlert is issued', 
      @category_name=N'[Uncategorized]', 
      @job_name=N'Alert Response Job' -- which was created before
GO

Once these three objects (user-defined message / job / alert) are created, we can now write the following test code to do the test.

In the following code, I will generate 6 errors with error number 50001 via the raiserror SQLstatement and this will generate 6 notification emails.

raiserror (50001, 10, 1) with log; -- we need with log to generate the alert
waitfor delay '00:00:02'; -- wait for 2 seconds
go 6

I will receive 6 emails as shown below.

Now let’s say, for multiple notifications within 1 minute, I want to receive the first 2 email notifications. Then I need to re-write the job step code as follows:

declare @cnt int, @time_window int = 1; -- 1 minute window
declare @allow_number int = 2; -- allow first two notifications only  
; with c as (
select run_date_time=msdb.dbo.agent_datetime(run_date, run_time) from msdb.dbo.sysjobhistory
where step_id = 0
and job_id =  $(ESCAPE_SQUOTE(JOBID))
)
select @cnt = count(*) from c
where run_date_time >= dateadd(minute, -1*@time_window, getdate());
declare @subject nvarchar(300);
if @cnt < @allow_number 
begin
   set @subject = 'alert response at ' + convert(varchar, getdate(), 8);
   exec sp_send_dbmail @recipients='[email protected]', @subject=@subject, @body='hello world'; -- replace [email protected] to your own 
end

/*
-- if you want to ignore the 1st email and only receive 2nd email, you can write the if clause as follows
if @cnt = (2-1) -- if you want to receive only the 3rd email, then it should be if @cnt = (3-1) 
  exec msdb.dbo.sp_send_dbmail @recipients='[email protected]', @subject='alert response', @body='hello world';
*/

I will only see the following two emails in my email account:

Now let’s change our business requirements to the following:

I only want to receive 3 continuous emails within 1 minute (actually you can define any time period)
Once I receive the 3rd email, I want to mark the email as high importance and CC to other people

For such requirements, I need to re-write the job notification step as follows:

-- on 3rd time, send an email with high priority and cc'ed to another email account

declare @cnt int, @time_window int = 1 -- 1 min
declare @allow_number int =3; 
declare @cc varchar(300)='[email protected]'; –- change accordingly  
declare @importance varchar(30) = 'normal';
declare @subject varchar(300);
; with c as (
select run_date_time=msdb.dbo.agent_datetime(run_date, run_time) from msdb.dbo.sysjobhistory
where step_id = 0
and job_id =  $(ESCAPE_NONE(JOBID))
)
select @cnt = count(*) from c
where run_date_time >= dateadd(minute, -1*@time_window, getdate());
if @cnt < @allow_number
begin
   set @importance = case  (@cnt+1) when @allow_number then 'high' else 'normal' end;
   set @cc = case (@cnt+1) when @allow_number then @cc else null end;
   set @subject = 'alert response - ' + cast((@cnt+1) as varchar);
   exec msdb.dbo.sp_send_dbmail  @recipients='[email protected]' –- change accordingly  
            , @subject=@subject
            , @body='hello world'
            , @copy_recipients = @cc
            , @importance = @importance;
end

In my Hotmail account, I see 3 notification emails, and the 3^rd has "importance" marked, i.e. the red exclamation mark.

While in my Yahoo account, I also receive a notification email (i.e. the 3^rd notification) as shown below.

So we have a customized notification mechanism to meet our business requirements, which otherwise cannot be realized via the native built-in mechanisms.

Summary

In this tip, we demonstrated how to customize our alert notifications to prevent email flooding and how to make our notification more flexible in meeting niche business requirements.

This is a proto-type demo, in real production the response job can be built for more functions other than just sending notification emails. For example, I will log each alert occurrence into a table, even though I do not send out notification for each alert. Later, I can generate a report detailing the frequency of the various alerts in each hour or each day/week/month. Or based on the alert occurrence time, I will check whether this alert is acknowledged after X times by checking another control table, which a DBA can call an acknowlegement stored procedure to add necessary messages, and if the alert is not acknowledged within X time, the email notification will be escalated by including higher level stakeholders in the notification loop, etc.

Next Steps

SQL Server alerts are an important and efficient tool for DBAs to monitor their SQL Server environment, when used creatively, it can help us reduce administration costs, avoid expensive 3^rd party tools or developing complex monitoring mechanisms.

Please read the following tips for more insights about SQL Server Agent Alerts:

About the author

Jeffrey Yao is a senior SQL Server consultant, striving to automate DBA work as much as possible to have more time for family, life and more automation.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Thursday, November 15, 2018 - 11:25:22 AM - jeff_yao	Back To Top (78259)
Glad it is of some help, thanks for the reading and comment, @Nisarg Upadhyay.