Database Mail Troubleshooting

Database Mail Troubleshooting can be tricky.

The first step is to Telnet to the SMTP server and see if you can send emails, and receive them.


After verifying that Telnet works, we usually start by getting the name of the profile and account details to test sending Email:

declare @subj nvarchar(100)
set @subj = 'Email from' + @@servername

EXEC sp_send_dbmail
, @recipients=''
, @subject=@subj
, @body='Please review this email Test 1'
--,  @file_attachments='E:\MSSQLDBA\Scripts\backup_report\log\bk_report.csv'

Then we would look at the actual queue:

-- Mail Queue: sent ITEMS
select * from  msdb.dbo.sysmail_mailitems
order by send_request_date desc

-- Display Error Queue
SELECT * FROM msdb.dbo.sysmail_event_log;


This query tracks specific emails and their error, if any

use MSDB
select a.mailitem_id, a.subject, a.body, a.sent_status, b.description
from sysmail_allitems a
left join sysmail_log b on a.mailitem_id = b.mailitem_id
order by a.mailitem_id desc

order by a.mailitem_id desc

Database Mail rarely breaks, but sometimes there are delays on the queue mails and you might need to clear the queue. In one occasion, I received the error:

The DB Mail gave the following error: Object Instance Not Set to an Instance of an Object.

The Maintenance Plans said:

Failed to acquire connection "Local Server Connection". Connection may not be configured correctly or you may not have the right permissions on this connection.

This happened right after a Windows Update that modified .Net Framework. Reverting to the old version or even repairing it fixed DB Mail

As I learn more about it, I'll be adding more

Leave a Reply

Your email address will not be published. Required fields are marked *