Today I noticed an issue where mail notification alerts to SQL agent operators was not working on one of our SQL Servers. No obvious errors in any of the logs and normal database mail was working with no problems.
Viewing the history of the operator I could not see any emails sent.
These are the steps I took to fix the issue.
1./ Dropped and recreated the operator group. I wouldn’t recommend this as any SQL jobs that have that operator set, lose it when you do this and it didn’t have any effect. Hopefully I can save you making the same mistake.
2./ After doing a bit of googling decided that restarting the SQL Agent service may help. Unfortunately it didn’t, still no change. Checking the sql server agent logs showed this error “Unable to start mail session (reason: No mail profile defined)”
3./ Finally I thought I had found the answer, go into SSMS right click on SQL Agent service, choose properites and choose the alert system link. Tick the enable mail profile box and I thought that would be it, but alas it still didn’t work. I was now getting the error “An attempt was made to send an email when no email session has been established”
4./ Further reading prompted me to again restart the SQL Agent service, as this needs to be restarted after the previous step is completed. Finally the emails began to work.
Hopefully this saves you some time should you ever face this issue!