'Check If sp_send_dbmail Was Successful

I am looking for a way to check if a particular e-mails queued using sp_send_dbmail are eventually successful sent from our Exchange server. I've looked at the system tables msdb.dbo.sysmail_mailitems and msdb.dbo.sysmail_log. msdb.dbo.sysmail_log seems to be the more helpful of the two; specifically, its description column. From the tests I've conducted so far, it seems whenever an error occurs, a message in the following format appears in the description column:

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2012-11-01T11:28:04). Exception Message: Cannot send mails to mail server. (Mailbox unavailable. The server response was: 5.7.1 Unable to relay for [email protected]). )

This is enclosed with other rows that share the same process_id. The description for the enclosing records are

DatabaseMail process is started

and

DatabaseMail process is shutting down

If an e-mail was successfully sent, the same 2 rows are recorded in the table, except with no enclosed rows between them.

So if I have a successful send, the following appears in the table

enter image description here

and if I have a send failure, the log records this

enter image description here

Are there other instances of how the entries could be logged if a send failed or if a send was successful? For example, could it be possible that there are 4 rows of entries for a send (2 enclosing stating when it was started and when it shut down, and 2 enclosed stating the e-mail was successfully sent). I've found no log records that diverged from the pattern listed above, but would like to be sure before I write logic based on this assumption.

sql-servertsql


Solution 1:[1]

sysmail_faileditems will only get you the list of failed emails. If you need to see a list of successfull emails you need to use sysmail_mailitems.

Use the below query to get details for all emails sent the same date:

SELECT * FROM msdb..sysmail_mailitems WHERE sent_date > DATEADD(DAY, -1,GETDATE())

And here is the complete query to get all the failed emails from the past 24 hours:

SELECT items.subject ,
       items.recipients ,
       items.copy_recipients ,
       items.blind_copy_recipients ,
       items.last_mod_date ,
       l.description
FROM   msdb.dbo.sysmail_faileditems AS items
       LEFT OUTER JOIN msdb.dbo.sysmail_event_log AS l 
                    ON items.mailitem_id = l.mailitem_id
WHERE  items.last_mod_date > DATEADD(DAY, -1,GETDATE())

Solution 2:[2]

  1. You can check mail is started or not using GUI. enter image description here

2)you check email sending using following command.

 **Select *  from msdb.dbo.sysmail_sentitems order by last_mod_date desc**

enter image description here

3) unable to relay in Exchange Server :- Launch Exchange Server Manager and move to Administrative Groups. Select Administrative Group Name then choose Server>Server Name. Now click on Protocols and Select SMTP.

Right-click on Default SMTP Virtual Server and select Properties

In Access tab, click on Relay>only the list below

Now check the checkbox “Allow all systems to successfully authenticate to relay regardless of the list above” and close. ....check with internal team for permission.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 TT.
Solution 2 Vishe

Related Questions