I discovered this little catch back when SQL 2005 released, but it took me awhile to notice it was not working. One of the cool new things in the 2005 release was database mail. Finally we didn’t have to setup mail profiles and install Outlook, which meant additional patching for Microsoft Office and possible reboots. We also no longer had to use an extended stored procedure to send email alerts from the database.
The group I worked in used Microsoft Operations Manager to monitor our environment, which included much more than just our SQL servers. One day I got an alert from MOM that one of my SQLAgent jobs failed on a job step. However, I never got an email alert from SQL server that the job had failed. I had already setup database mail and had even run my code in SSMS, which ran fine and sent email via msdb.dbo.sp_send_dbmail. In fact, I had even recently received an email from that job and verified that kicking off the job manually resulted in success. So I started wondering what could be the problem since I knew database mail was working and verified it both by executing code in SSMS and via SQLAgent job. I also started wondering why I had not noticed this before when it dawned on me that my jobs were set to only send email in the event of a failure. Well, I would like to think it was skill and not luck that I had not had any job failures up until then. I’m glad we were using MOM or who knows when I would have noticed the job failures. This server was a development server that rarely got paid any attention.
After some troubleshooting I started to search the internet to see if anyone else had run across this issue. My hours of searching turned up nothing so I went back to the troubleshooting drawing board. Keep in mind this was right after SQL 2005 released so there very well may be plenty of search results for it now. What I discovered was that the SQLAgent itself has to be mail enabled and assigned to a database mail profile. The fix was quite simple. In SSMS right click SQL Server Agent and select properties. In the left hand pane select the Alert System page. Next we need to check off “Enable Mail Profile”. Next we select the mail system from the drop down box and select Database Mail. If you are still using SQLMail you can also select that, but I highly recommend you start migrating your jobs and any code over to Database Mail. Lastly we select which mail profile setup in Database Mail that we want to use for sending job messages from SQL Server Agent.
You must be logged in to post a comment.