Sql-server – Can send test database mail but job emails failing

database-mailsql serversql-server-2005

I stopped receiving sql agent job failure notifications in the recent past. Upon investigation the error reported is:

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2017-08-16T02:40:14). Exception Message: Could not connect to mail server. (No such host is known).

The mail account setup has not been changed and I verified it looks correct. Futhermore, I can successfully send and receive an email using the Send Test Email… function in SSMS. So why can't the jobs send the emails out?

Since the Send Test Email… function queues the email, it doesn't seem as there would be a permissions issue with sending it this way vs the job sending it. Also, that would indicate it is not a firewall issue. So the only difference is time of day…the jobs run in the middle of the night…but I can't think of any system configuration that would be changing then.

Any other ideas of things to check?

Best Answer

I can think of two possibilities: the user who runs the SQL Server jobs may not have the right permissions (due to changes on the mail server end, possibly); or, the server itself has been down at night for some reason.

I suggest you create a job that will fail for certain, and run it via a schedule during the day, and at night. If it fails during the day, then the permissions for the SQL Server Agent user may no longer be correct for sending mail. If it succeeds during the day and fails at night, then maybe there's something going on with the mail server itself.

Note the error you reported:

Could not connect to mail server. (No such host is known).

That sounds like the mail server can't be found.

Related Solutions

Sql-server – SQL Server Database Mail failing to send emails with error Sender address rejected: Access denied

Perhaps the IP of your second node is not allowed to relay.

Sql-server – Any way to send notifications to different Emails on success/failure of SqlServerAgent job

You have to use tsql to do that.

in the job step after your main job step add 2 more job steps:

enter image description here

step 2--success email

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'YOUR MAIL PROFILE',
    @recipients = 'COMPANYMAIL@COMPANY.COM',
    @body = 'Copy and Purge BAKs success !!',
    @subject = 'Copy and Purge BAKs success',
-- to send file attachments 
    @file_attachments = 'D:\logs\log.TXT',
  -- Change below if you want to include query results from a table in the email. 
  -- Note that below is not in HTML
    @query = 'select column1, column2 from table1';

step 3-- failure email

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'YOUR MAIL PROFILE',
    @recipients = 'COMPANYMAIL@COMPANY.COM',
    @body = 'Copy and Purge BAKs failed !!',
    @subject = 'Copy and Purge BAKs failed',
-- to send file attachments 
    @file_attachments = 'D:\logs\log.TXT',
  -- Change below if you want to include query results from a table in the email. 
  -- Note that below is not in HTML
    @query = 'select column1, column2 from table1';

and then use tsql sp_send_dbmail

Note: If you want HTML email to be send out, I have a script for that to help you out.

Best Answer

Related Solutions

Sql-server – SQL Server Database Mail failing to send emails with error Sender address rejected: Access denied

Sql-server – Any way to send notifications to different Emails on success/failure of SqlServerAgent job

Related Question