Database Mail not sending notifications

Kaplan, Andrew H 101 Reputation points
2022-09-07T15:31:41.543+00:00

Hello.

We are running SQL Server 2012 on a Windows 2012 R2 platform. I have configured Database Mail to send notifications to a group email account when backup jobs complete for the Full and Differential backups, and when the jobs fail for the log backups. The setup is identical to that on another server, and I have confirmed that notifications should work by running the test email.

The problem is that no emails are being sent when a given job completes. A check of the history has the following entry: NOTE: Failed to notify '<group name>' via email. I have modified the recipient to a single user, as opposed to a group, email account, and the result is the same.

I have confirmed the run_value for Database Mail XPs is set to 1, and for good measure, I did restart the program. One disparity that I noticed is principal profile entries between the two servers. The profile on the system that works is the following:

principal_id principal_name profile_id profile_name is_default
2 guest 1 SQLAccount 1

While that on the system that is not working is as follows:

principal_id principal_name profile_id profile_name is_default
2 guest 2 RODB Admins 1

If the profile_id and profile name is different, could that be the cause of the problem? If so, how can this be corrected?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,589 questions
{count} votes

4 answers

Sort by: Most helpful
  1. Kaplan, Andrew H 101 Reputation points
    2022-09-07T17:13:17.93+00:00

    Hello.

    I went through the process of recreating the profile. After that was done, I went to configure the notification when a job completes, and I noticed something. The changes that I made were not being retained. Simply put, after I set up email notification to the given email address, and I click OK, the changes are not kept. Hence, the emails will not be sent.

    What would cause this?


  2. Kaplan, Andrew H 101 Reputation points
    2022-09-07T18:24:05.957+00:00

    Hello.

    I ran the above query, and the output in Studio read as follows:

    Mail (Id: 13) queued.

    As of this posting, which is being done five minutes after the above action, the test email has not been received.


  3. Kaplan, Andrew H 101 Reputation points
    2022-09-07T18:37:46.76+00:00

    Hello.

    The filtered results of the query are shown below. There were only four lines of information.

    SentDate Reason description mailitem_id profile_id recipients copy_recipients blind_copy_recipients subject body body_format importance sensitivity file_attachments attachment_encoding query execute_query_database attach_query_result_as_file query_result_header query_result_width query_result_separator exclude_query_output append_query_error send_request_date send_request_user sent_account_id sent_status sent_date last_mod_date last_mod_user
    2022-08-01 08:01:35.230 failed The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2022-08-01T08:02:36). Exception Message: Cannot send mails to mail server. (Failure sending mail.).
    ) 5 1 <email address> NULL NULL Database Mail Test This is a test e-mail sent from Database Mail on <database instance>. TEXT NORMAL NORMAL NULL MIME NULL NULL 0 1 256 0 0 2022-08-01 08:01:35.230 <user account> NULL failed 2022-08-01 08:17:14.763 2022-08-01 08:17:14.763 <user account>
    2022-08-01 08:01:35.230 failed The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2022-08-01T08:01:36). Exception Message: Cannot send mails to mail server. (Failure sending mail.).
    ) 5 1 <email address> NULL NULL Database Mail Test This is a test e-mail sent from Database Mail on <database instance>. TEXT NORMAL NORMAL NULL MIME NULL NULL 0 1 256 0 0 2022-08-01 08:01:35.230 <user account> NULL failed 2022-08-01 08:17:14.763 2022-08-01 08:17:14.763 <user account>
    2022-07-28 09:40:30.903 failed The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2022-07-28T09:41:31). Exception Message: Cannot send mails to mail server. (A recipient must be specified.).
    ) 2 1 Test NULL NULL Database Mail Test This is a test e-mail sent from Database Mail on <database instance>. TEXT NORMAL NORMAL NULL MIME NULL NULL 0 1 256 0 0 2022-07-28 09:40:30.903 <user account> NULL failed 2022-08-01 08:17:14.763 2022-08-01 08:17:14.763 <user account>
    2022-07-28 09:40:30.903 failed The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2022-07-28T09:40:31). Exception Message: Cannot send mails to mail server. (A recipient must be specified.).
    ) 2 1 Test NULL NULL Database Mail Test This is a test e-mail sent from Database Mail on <database instance>. TEXT NORMAL NORMAL NULL MIME NULL NULL 0 1 256 0 0 2022-07-28 09:40:30.903 <user account> NULL failed 2022-08-01 08:17:14.763 2022-08-01 08:17:14.763 <user account>

    0 comments No comments

  4. Seeya Xi-MSFT 16,436 Reputation points
    2022-09-08T02:35:16.22+00:00

    Hi @Kaplan, Andrew H ,

    Welcome to Microsoft Q&A!
    Please see this more complete troubleshooting: General database mail troubleshooting steps.
    You must be a member of the sysadmin fixed server role to troubleshoot all aspects of Database Mail. The troubleshooting is as follows:

    • Is database mail enabled
    • Are users properly configured to send mail
    • Is database mail started
    • Do problems affect some or all accounts
    • Retry mail delivery

    Best regards,
    Seeya


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.