0

We have a SQL Agent job that runs daily that sends emais out to our drivers with their schedules for the next day.

For 85% of drivers, the emails are not getting out of the SQL server. The other 15% send without error.

I checked the logs and see this error:

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2020-03-22T16:00:06). Exception Message: Cannot send mails to mail server. (Failure sending mail.). )

Does anyone have any idea what's wrong, or how to get a more detailed error?

This problem started after we migrated the server to a new datacenter.

We are running SQL Server 2014 on a Windows Server 2008 R2 host.

2
  • 1
    Is it the same 15% that are successful each day? – Tony Hinkle May 12 '20 at 17:48
  • What kind of the mail server are you using? Most probably you will have some errors on the mail server itself, could you post them. – Piotr May 12 '20 at 19:31
1

Adding to the SQL Mail Fail to Send quoted in the comments by LowlyDBA

From this question:

Comprehensive troubleshooting of DatabaseMail

I would first of all see what is the outcome of the following query (as you already got dbmail up and running):

--==============================================================
-- I’m doing a TOP 100 on these next several queries as they tend
-- to contain a great deal of data.  Obviously if you need to get
-- more than 100 rows this can be changed.
-- Check the database mail event log.
-- Particularly for the event_type of "error".  These are where you
-- will find the actual sending error.
--==============================================================
SELECT TOP 100 * 
FROM msdb.dbo.sysmail_event_log 
ORDER BY last_mod_date DESC;


--==============================================================
-- Check the actual emails queued
-- Look at sent_status to see 'failed' or 'unsent' emails.
--==============================================================
SELECT TOP 100 * 
FROM msdb.dbo.sysmail_allitems 
ORDER BY last_mod_date DESC;


--==============================================================
-- Check the emails that actually got sent. 
-- This is a view on sysmail_allitems WHERE sent_status = 'sent'
--==============================================================
SELECT TOP 100 * 
FROM msdb.dbo.sysmail_sentitems 
ORDER BY last_mod_date DESC;


--==============================================================
-- Check the emails that failed to be sent.
-- This is a view on sysmail_allitems WHERE sent_status = 'failed'
--==============================================================
SELECT TOP 100 * 
FROM msdb.dbo.sysmail_faileditems 
ORDER BY last_mod_date DESC

Depending on the outcome of these queries, there are a few ways to troubleshoot.

Maybe the problem is not within sql? for that I use powershell to send a few test emails see if everything is ok. (change the details in the script below)

$smtpServer = "200.1.1.223"
$smtpPort = 25
$emailFrom = "donotreply@xxxxx.co.uk"
$emailTo = "mmiorelli@xxxxx.co.uk"
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$smtp.Port = $smtpPort
$subject = "ccatsql" 
$body = "test email from ccatsql " 
$smtp.Send($emailFrom, $emailTo, $subject, $body)

check if the .Net framework 3.5 is installed - that can cause weird errors, even in sql server 2016, as quoted in

SQL Server 2016 - Database Mail doesn't work without .NET 3.5

and from my own experience, sometimes, no error will be written to any log whatsoever, if the .NET 3.5 or any underlying component is missing or corrupt.

To work around this issue (ONLY if this is the issue), you can implement any one of the following:

  1. Create the DatabaseMail.exe.config and drop it next to the DatabaseMail.exe under the Binn folder.

You can use notepad.exe or any other editor to edit it.

Just make sure you save it by using UTF-8 encoding (in notepad.exe, select Save As... and in the Encoding combo box, select UTF-8):

  1. Run a repair setup action of SQL Server 2016.

  2. Manually install .Net Framework 3.5 on the machine.

please see more information on this answer.

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