Do you know if Database Mail is Having Problems?

I have a few business processes that depend on Database Mail sending information to staff at the appropriate time.  I’ve found that Database mail can experience problems sending emails and those problems can be hidden.

Open MailboxWhen you use Database Mail you execute sp_send_dbmail and this passes the information you send in to a mail queue.  The result of the procedure call is “Mail Queued”.

It is possible for the queued mail to fail or to never be sent and your business process to be unaware because the “Mail Queued” result was received.  The query below will interrogate the sysmail tables in msdb to find email attempts that failed or have never be sent so you can troubleshoot Database Mail issues you may not have known you have.

 

USE msdb
go
 
SELECT  ServerName = @@SERVERNAME
      , ProblemType = 'Failed email'
      , MailItemId = si.mailitem_id
      , Recipients = si.recipients
      , CopyRecipients = si.copy_recipients
      , BlindCopyRecipients = si.blind_copy_recipients
      , SendRequestUser = si.send_request_user
      , SentDate = si.sent_date
      , LogId = el.log_id
      , EventType = el.event_type
      , LogDate = el.log_date
      , ErrorDescription = CAST(el.[description] AS varchar(max))
      , [Subject] = si.[subject]
      , Body = si.body
FROM    dbo.sysmail_faileditems AS si
        LEFT JOIN dbo.sysmail_event_log AS el ON si.mailitem_id = el.mailitem_id
UNION ALL
SELECT  server_name = @@SERVERNAME
      , problem_type = 'Unsent (queued) email'
      , si.mailitem_id
      , si.recipients
      , si.copy_recipients
      , si.blind_copy_recipients
      , si.send_request_user
      , si.sent_date
      , el.log_id
      , el.event_type
      , el.log_date
      , error_description = CAST(el.[description] AS varchar(max))
      , si.[subject]
      , si.body
FROM    dbo.sysmail_unsentitems AS si
        LEFT JOIN dbo.sysmail_event_log AS el ON si.mailitem_id = el.mailitem_id
ORDER BY si.sent_date DESC

Leave a Reply

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