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.