Monitoring SQL Server Agent jobs and getting alerts is one of the most important ways we can react to any instance or server issues. Database Mail is great! It’s our friend that can let us know when SQL Server isn’t feeling quite right and needs some care and attention.
Setting up database mail and operators is something you’ll probably do immediately after a new instance is installed and therefore is not something done very often unless your job has the unlikely title of database mail configurator.
So you run your script and an operator magically appears. You configure your profiles and accounts and then test your configuration and an email arrives in your monitoring mailbox proudly declaring “This is a test e-mail sent from Database Mail”.
So we set up notifications on our alerts and we’re all set. Or are we… The alert didn’t fire the notification. We forgot to enable the mail profile. If we’re doing it as a one off in SSMS then right click on SQL Server Agent, go to Alert System and tick enable mail profile.
If you setup your database mail by script then most likely you already have this enabled. Now we’re set! But still no notification email appears. There is one final step for you, Restart SQL Server Agent. Now we’re set? Yes! The notification email arrives in your inbox and you can now set up the rest of your instance and give SQL Server some help from Database Mail.
Now, restarting your SQL Server Agent may not be an issue at the build and configure stage but it could be difficult later on, so why is it required? According to Books Online, SQL Server Agent caches the profile information and doesn’t use any new information until the restart. It does this so that the fail-safe operator may be notified if the SQL Server instance becomes unavailable.
Database Mail is your friend