If you have configured Database Mail correct in the Management Studio wizard in SQL Server 2005 (yes, some still use that old but stable chump), with a profile connecting to a user etc, and also have set up a job so that a notification should be sent to you, when the job fails. Then you think you are done? Nope. You’re not done yet.
There are this (agent) service settings to be made as well.
I read this at the end of a errormessage when a job failed, when I didn’t get an errormail of the fact:
…”NOTE: Failed to notify ‘BI_Mailbox’ via email.”
Followed some google advices, and for example, simply restarting SQL Server Agent did not work for our server.
This did the trick though, not hard to do at all 🙂 had just forgot/did not know I needed to enable mail session in the agent, and unfortunately that requires a restart of the agent service before it works. A task that sometimes needs lots of negotiations and getting approvals, which is not always easy in heavily used environments. You don’t want to be forced to wait for a service window a week later for the for the restart so job mails start working. Many admins know what I’m talking about:
1. In Management Studio, right-click SQL Server Agent again and select ”Properties.”
2. Click ”Alert System”
3. Click ”Enable mail profile”
4. Click ”OK”
5. Restart SQL Server Agent.