Avoiding 'Failed to notify via email' errors

Ref:

http://sqlmag.com/blog/avoiding-failed-notify-operator-email-errors

If a picture is worth a thousand words, then the words associated with the following picture would probably involve a decent amount of muttering and cursing about the the DREADED ‘Failed to notify <operator name> via email’ error message that you might see crop up in your environment every once in a while – on new or recently provisioned servers:

Email Alerts Good. Configuration Options – Not so Intuitive

As I’ve posted in the past, using Database Mail can be a great way to be notified of problems or issues that might occur with your server (such as corruption or IO problems) or with your jobs – provided, of course, that you’ve got some sort of backup or ‘watchdog’ for your alerting system to make sure that it doesn’t just fail silently without any warnings.

Only, as great as it is to set up a Notification for an operator (I highly recommend using an alias instead of binding actual/individual email-addresses to so-called operators) to be notified of when a job fails or runs into problems, there’s an extra configuration step that you’ll need to jump through to get this to work.

And the problem is that this extra/additional configuration step is obscenely easy to forgot. In fact, I’ve forgotten about it enough times now that being burned by this issue with new servers that I’ve provisioned (or recently inherited) has caused me to blog about this step – in the hopes that doing so will ‘fuse’ this step into my brain. That, and this extra step is so easy to forget about that I’m sure that posting it here will help someone else.

Letting the SQL Server Agent Talk to Database Mail

As you might guess, getting Database Mail to play nicely with the SQL Server Agent is sadly a question of security. Or, stated differently, you’ll get the dreaded ‘Failed to notify … via email’ errors IF you haven’t explicitly allowed the SQL Server Agent to interact with Database Mail.

That said, it’s happily a trivial operation to allow the SQL Server Agent and Database Mail to ‘get giggy’ with each other and throw wild little parties for one another – whenever they want or need to. To do so, you just need to right click on the SQL Server Agent node in SQL Server Management Studio, and select Properties. Then switch to the Alert System node, and enable the Mail profile you’d like to allow the SQL Server Agent to use, restart the SQL Server Agent Service (NOT the SQL Server or the box – just the SQL Server Agent Service – which you can pretty much safely do on MOST systems at ANY time without any big worries or concerns), and you’re good to go.