I setup a test job to test out my failure notifications and now I'm getting the following error, stating it did not notify my operator via email. The strange thing is I ran the same exact setup script on several other instances and it worked just fine, but is failing on this instance. I checked the properties for SQL Server Agent and the operator itself. I also sent myself a test email from the Management branch's Database Mail and it worked successfully.
Failed to notify 'DBA Team' via email.
Ensure you've enabled Mail Session in SQL Server Agent.
SQL Server Agent > Properties > Alert System: Check Enable mail profile.
This link should help: Avoiding failed notified operator email errors
I've experienced an issue on several instances that occasionally causes SQL Server Agent to stop processing outgoing mail requests.
See this Microsoft Connect article which seems to indicate this problem may have been fixed in SQL Server 2008 R2 Service Pack 3 and later..
The simplest mitigation for this problem is to simply restart the SQL Server Agent. Note, you do not need to restart SQL Server itself, only restart the Agent.
Doing a quick search of Microsoft Connect shows several other potential issues around sending email from the Agent.
I had the same issue on a fresh SQL Server instance, and apart from enable the Mail Session as wjm said, I needed to set Profile I was using as the Default Public Profile:
Management > Database Mail > Configure Database Email > Manage Profile Security > Set 'Yes' in 'Default Profile' on the profile you are using.
