Skip to main content
Dave Mason - Mastodon Dave Mason - LinkedIn Dave Mason - GitHub Dave Mason - Counter Social

SQL Server Alerts and Conditional Responses

SQL Server event alerts that respond by notifying operators can lead to too much noise in your email inbox. There is a built-in option to specify a number of minutes to delay between responses. For many, that setting alone is sufficient to keep the spam down to a minimum. But events may still occur during that "delay between responses" window. And SQL Server will fire alerts for them. You may not always want the response suppressed.

SQL Server provides another way to more selectively respond to alerts: by executing a SQL Agent job, and using tokens within the job steps. To use tokens, you'll need to enable their use. Open SQL Server Agent Properties, select the Alert System page, and check the "Replace tokens for all job responses to alerts" checkbox as seen here:

SQL Agent Properties - Alert System

Now let's look at a typical alert. The one pictured below raises alerts for errors with a severity level of 20. On the Response page, we see the alert is configured to respond by executing a job. (From this dialog you can choose an existing job, or click to create a new one.) The Notify Operators option is left unchecked.

SQL Server Alert Properties - General
SQL Server Alert Properties - Response

Within the "DBA-Catch Severity 20 Alert" SQL Agent job, T-SQL job steps that use tokens can be created. Here is an example: 


DECLARE @Descr VARCHAR(MAX) = '$(ESCAPE_SQUOTE(A-MSG))';

IF @Descr LIKE '%CLIENT: 127.0.0.1%'
BEGIN
    --Do nothing.
    PRINT 'Alert originating from pen test server.'
END
ELSE
BEGIN
    DECLARE @To NVARCHAR(MAX) = 'DMason@xyz.com';
    DECLARE @Subj NVARCHAR(255);
    DECLARE @EmailBody NVARCHAR(MAX);
    DECLARE @Date VARCHAR(MAX) = '$(ESCAPE_SQUOTE(DATE))';
    DECLARE @Time VARCHAR(MAX) = '$(ESCAPE_SQUOTE(TIME))';

    SET @Subj = 'SQL Server Alert System: ''20-Fatal Error in Current Process''' +
        'occurred on ' + @@SERVERNAME
    SET @Date = SUBSTRING(@Date, 5, 2) + '/' + RIGHT(@Date, 2) + '/' + LEFT(@Date, 4);
    SET @Time = LEFT(@Time, 2) + ':' + SUBSTRING(@Time, 3, 2) + ':' + RIGHT(@Time, 2);
    SET @EmailBody = 'DATE/TIME: ' + @Date + ' ' + @Time + '

DESCRIPTION: ' + @Descr + '


COMMENT: (None)

JOB RUN: (None)'
 
    EXEC msdb..sp_send_dbmail
        @recipients = @To, 
        @subject = @Subj,
        @body = @EmailBody
END

There are three tokens within the T-SQL (highlighted in yellow above): A-MSG, DATE, and TIME. SQL server replaces these three tokens as follows:

  • A-MSG: Message text. If the job is run by an alert, the message text value automatically replaces this token in the job step.
  • DATE: Current date (in YYYYMMDD format).
  • TIME: Current time (in HHMMSS format).
    • See the MSDN documentation for a list of tokens and their descriptions.

    Note the conditional logic, checking the error message. If it originates from a server with a particular IP address, no action is taken. Otherwise, an email is sent with the relevant error information. (This is a real-world example involving recurring penetration tests. The pen test server conducted port scans on the SQL host and attempted numerous connections as [sa]. I got an onslaught of unwanted email.)

    Tokens are a powerful tool that give you a lot of flexibility. This very simple example just scratches the surface of what is possible. Use your imagination. Get creative. Happy alerting!

    Comments

    Post comment