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 @profile_name='DBA_PROFILE' , @firstname.lastname@example.org' , @subject=@subj , @body='Please review this email Test 1' --, @file_attachments='E:\MSSQLDBA\Scripts\backup_report\log\bk_report.csv' go
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