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:
- 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):
<br>
</p>
Run a repair setup action of SQL Server 2016.
Manually install .Net Framework 3.5 on the machine.
please see more information on this answer.
