How to Configure SQL Server Agent Alerts

There are a couple of different checks in sp_Blitz® involving alerts.

One possibility is that no SQL Server Agent alerts have been configured.  This is a free, easy way to get notified of corruption, job failures, or major outages even before monitoring systems pick it up.  This part of our SQL Server sp_Blitz script checks msdb.dbo.sysalerts looking for records.

Another common problem is that SQL Server Agent jobs have been configured, but have not been set up to alert an operator if they fail.  You’ll need to go into SSMS, right-click on the Agent job, and click Properties to configure the alerts.

To Fix the Problem

The below script sets up SQL Server Agent alerts for severity 16 through 25 as well as specific alerts for 823, 824 and 825 errors.  Do a search & replace in this code and change ‘The DBA Team’ to the name of your SQL Agent operator – and remember, always use email distribution lists for your operator emails, not individual peoples’ emails.  Someday – not soon, but someday – you’re going to go on vacation, and you want other people to get notified when you’re gone.

Shout-out to @KeefOnToast for helping improve this script with more alerts, too:

For steps to test the alert, check out our SQL Server Setup Guide.

After this change, you’ll be able to sleep at night knowing your SQL Server is working well.  Or you’ll be awakened constantly by the sound of your phone because SQL isn’t working well.  Either way, you’re a winner.

4 Comments. Leave new

  • you should add drops for who already have old version of this alerts

  • Hi:

    First of all sp_blitz is Awesome!
    Now I did created the alerts with the script you gave in the web page (http://BrentOzar.com/go/alert) but it stills tells me there’s no alerts for errors 823, 824, and 825.
    I thought they were covered under the Severity level 024 alerts but now I’m in doubt.
    Is this right?
    Thanks for all the help!
    FJM

  • I’m experiencing the same thing as Francisco.
    The script is looking for message_id 823, 824, 825. All my alerts (severity 16-25) have message_id 0
    Am I missing the right alerts, or interpreting the sp wrong?

  • Tammy Prater
    July 25, 2012 12:12 am

    Can using SQL Server event alerts cause any performance overhead or performance issues?
    Thanks.

  • FYI, the script is missing 3 semicolons for the last 3 errors (at the end of the @job_id line of code). Also note that the script, as is, using @delay_between_responses=60 will send notification every minute and @notification_method = 7 will notify the named operator via email (1), pager (2), and Net send (4). (I changed my notification_method to 1 for email only)

Menu
{"cart_token":"","hash":"","cart_data":""}