If you’re trying to send mail with SQL Server but it’s not being delivered, try checking the sysmail_faileditems view.

Example

Here’s an example of checking for failed mail. Note that it needs to be run on the msdb database.

SELECT * FROM msdb.dbo.sysmail_faileditems;

Result (using vertical output):

mailitem_id                 | 1
profile_id                  | 1
recipients                  | [email protected]
copy_recipients             | NULL
blind_copy_recipients       | NULL
subject                     | SQL Server Agent Job: FAILED
body                        | Your favorite SQL Server Agent job just failed
body_format                 | TEXT
importance                  | NORMAL
sensitivity                 | NORMAL
file_attachments            | NULL
attachment_encoding         | MIME
query                       | NULL
execute_query_database      | NULL
attach_query_result_as_file | 0
query_result_header         | 1
query_result_width          | 256
query_result_separator      |  
exclude_query_output        | 0
append_query_error          | 0
send_request_date           | 2020-08-24 02:40:48.093
send_request_user           | sa
sent_account_id             | NULL
sent_status                 | failed
sent_date                   | 2020-08-24 02:41:53.000
last_mod_date               | 2020-08-24 02:41:53.290
last_mod_user               | sa

I used vertical output here so that you don’t need to scroll sideways to see all columns.

In this case, I’m showing one failed email. I actually had two failed emails but I decided that it was more concise to show just one result.

In my case, I had used the wrong mail server in the code that sends the email, and the emails failed. As soon as I updated the code to use the correct mail server, all subsequent emails were sent successfully. However, this doesn’t change those that had already failed, and therefore, they remain in the sysmail_faileditems view.

You can query sysmail_unsentitems to return a list of unsent emails (ones that are yet to be sent, not necessarily failed).

You can also query sysmail_sentitems to get all sent emails.

You can also query sysmail_allitems to get all emails (sent, unsent, failed, and retrying).