When sending emails from SQL Server, you can check for any unsent mail with the sysmail_unsentitems view.

Example

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

SELECT * FROM msdb.dbo.sysmail_unsentitems;

Result (using vertical output):

mailitem_id                 | 4
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 04:11:19.300
send_request_user           | sa
sent_account_id             | NULL
sent_status                 | unsent
sent_date                   | NULL
last_mod_date               | 2020-08-24 04:11:19.300
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, there’s one unsent email. I happened to run this query immediately after executing msdb.dbo.sp_send_dbmail to send this mail.

As it turned out, the mail was sent immediately after I copied the above results, and now when I run that query again, I get zero results (i.e. there are no unsent emails).

SELECT * FROM msdb.dbo.sysmail_unsentitems;

Result:

(0 rows affected)

However, just because there are no unsent emails, it doesn’t mean that there are none that failed.

You can query sysmail_faileditems to return a list of failed emails.

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).