I’ve seen many people go through the trouble of setting up database mail and configuring SQL Agent Alerts only to realize it’s not working. The reason in the cases I’ve seen is because they have simply neglected to assign a mail profile to SQL Agent. This is way more common than you would think, thus I wrote this quick blog.
This check box and drop down can sometimes lead to hours of troubleshooting if you don’t know where to look. In order to receive the alerts, you must enable a mail profile. This would be the profile created during the Database Mail configuration process.
To configure SQL Server Agent to use Database Mail
- In Object Explorer, expand a SQL Server instance.
- Right-click SQL Server Agent, and then click Properties.
- Click Alert System.
- Select Enable Mail Profile.
- In the Mail system list, select Database Mail.
- In the Mail profile list, select a mail profile for Database Mail.
- Restart SQL Server Agent.
There are many reasons why Database Mail may fail to send Alerts, but make sure you have this configuration set before spending hours trouble shooting.
A Side Note:
If you want to learn how to setup your alerts and operators I’ve already written a blog on that with scripts you can find it here.