Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

Next Webcast - Simple SQL Server Reporting - Click Here to Register
 

Troubleshoot SQL Server Agent Notifications and Database Mail


By:   |   Read Comments (2)   |   Related Tips: More > SQL Server Agent

Problem

Recently when we came into the office and started reviewing daily alerts and we found that one of our business critical production environments did not send an alert for one of the critical SQL Server Agent Jobs. This SQL Server Agent Job feeds data to our FINANCE application dashboard for the current trends. What should we review in SQL Server to determine the root cause of the issue?

Solution

To resolve the issue we used the following steps:

Step 1 - Validate SQL Server Database Mail

First test to see if the system is able to send email using the Database Mail profile in SQL Server Management Studio by navigating to Management > Database Mail, then right clicking on Database Mail and selecting the "Send Test E-Mail..." option.

Once the interface loads, select the correct Database Mail Profile, enter the To email address, Subject and Body. Once complete press the "Send Test E-Mail" button.

In our case, the SQL Server Database Mail test was successful hence it shows the ExternalMailQueue is enabled and there is no issue with the SMTP port being blocked by a firewall rule.

To check the external mail queue is enabled, you can use the following code:

SELECT * FROM msdb.sys.service_queues
WHERE name = N'ExternalMailQueue'

Step 2 - Validate SQL Server Operator

As the above test was successful, we next looked to see if someone had removed the Operator from SQL Server Agent in SQL Server Management Studio by navigating to SQL Server Agent > Operators. Our Operator was setup and the properties (not shown below) were correct.

In our case, the operator still exists, the properties were correct, etc., so this was not the root cause of our issue.

Step 3 - Validate the SQL Server Agent Mail Profile

After the above two steps were not the issue, we concluded the issue was with SQL Server Agent. We thought about restarting SQL Server Agent, but since Jobs were completing successfully we started looking into the SQL Server Agent Properties. The Properties can be found in SQL Server Management Studio by navigating to SQL Server Agent then right clicking on SQL Server Agent to select the Properties option.

As notification is tied to the Alert system we looked at the “Alert System” tab. Looking at the properties we were able to figure out the issue. It was very clear that even though we have a Database Mail Profile setup it was not enabled for SQL Server Agent as shown below.

Step 4 - Enable Mail Profile in SQL Server Agent

To correct the situation, check the “Enable Mail Profile” check box and select the Mail System and Mail Profile in the interface as shown below or use the following command to enable the Database Mail Profile for SQL Server Agent.

USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties
                 @email_save_in_sent_folder=1,
                 @databasemail_profile=N'DBAxxxxx', -- put your database mail profile here
                 @use_databasemail=1
GO

Step 5 - Restart SQL Server Agent

Once the Database Mail Profile is enabled it is a best practice to restart the SQL Server Agent service and then test the notification by running a job that includes notification. However in our case we didn’t restart the service, but tested the notification by running a job.

Next Steps


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Varinder Sohal Varinder Singh is a SQL Server production DBA in the Boston area with 20+ years of database, SSIS, SSAS and SSRS administration experience.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, July 12, 2018 - 1:34:42 PM - Andy K. Back To Top

 YOU are my hero! Thank you!!! 


Friday, February 12, 2016 - 12:31:15 AM - Ravindra Kumar Back To Top

 Awesome ..:) WOW

Actually we need html formatted mail.

Could you please suggest how we send it?

 

Regards,

Ravindra Kumar

 


Learn more about SQL Server tools