30

I have a problem with sending notification when the job fails. I created Database Mail Profile and assigned it to SQL Agent and the restarted SQL Agent. When I run a procedure msdb.dbo.sp_send_dbmail the e-mail is being sent. Also when I send a test e-mail using Database Mail it works fine. But when I set a notification for Job when it fails, in job log I see following information: NOTE: Failed to notify 'SQL Job Failure' via email. I don't know what can be the source of this problem. I found this hint: http://sqlblog.jfperry.com/jpsql/post/2009/04/07/NOTE-Failed-to-notify-7boperator7d-via-email.aspx

But I checked that the profile is enabled in SQL Agent. SQL Agent has been restarted. Where can be the source of the problem?

Improve this question

6 Answers 6

Reset to default
59

As you allude to in your question, my issue was indeed the mail profile in agent - thanks for the tip.

enter image description here

Other tips that are mentioned at the bottom of this post include:

  1. Ensure DB mail is enabled within the SQL Server Surface Area Configuration. Do this by right clicking the server name and selecting FACETS from the menu Select the 'Server Configuration' from Facet dropdown Ensure Facet Property of 'DatabaseMailEnabled' is True

  2. Set up a Mail Profile within the Management Folder of your server

  3. Within SQL Server Agent set up an Operator

  4. Look in the properties of the SQL Server Agent (right-click properties) and under the Alert System page tick 'Enable mail profile' and select your default profile that you set up in step 2.

  5. Restart the SQL Server Agent

Improve this answer
1
  • 1
    @Microsoft, SQL Server email notification is kind of important to us. Can you make it a little easier to set up and more reliable?
    – Baodad
    Commented Jul 14, 2015 at 15:37
7

I had to grant the SQL Server Agent access to the mail profile.

Right click on the SQL Server Agent node in SQL Server Management Studio, and select Properties. Then switch to the Alert System node, and enable the Mail profile you’d like to allow the SQL Server Agent to use, restart the SQL Server Agent Service

Improve this answer
6

Second restart of SQL Agent fixed the problem.

Improve this answer
1

This worked for me in SQL Server 2014:

Go to the properties of the SQL Agent Job, choose the Notifications page, if the email checkbox is ticked then untick it, apply that change. Then go back into the job and re-tick the box and select your Operator, then apply again.

enter image description here

I think the original problem was caused by messing about with the DbMail Profiles, so unhooking and re-hooking the link to the Operator seemed to flatten things back out.

Improve this answer
1
  • 1
    Also applicable to SQL Server 2017
    – Chris Driver
    Commented Jul 1, 2020 at 13:41
0

I followed below solutions on SQL Server 2019.

First Solution is restarting the SQL Server Agent.

Some of the SQL Servers are still faced issue after restarted the SQL Server Agent as well.

The second solution is -> SQL Server Agent Properties -> AlertSystem -> "Untick the Enable Mail Profile" if it ticked already -> Click OK. Again open, SQL Server Agent Properties -> AlertSystem -> "Tick the Enable Mail Profile" Click OK

It worked for me.

Improve this answer
0

I did find for me none of the solutions worked but re-applying the database profile did fix my issue:

USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1, 
        @databasemail_profile=N'MyDatabaseProfile', 
        @use_databasemail=1
GO
Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.