If you use Database Mail in SQL Server, you can use the sysmail_allitems view to check the status of all emails that Database Mail has processed.

Example

Here’s an example of checking the status of all mail message. Note that this needs to be done on the msdb database.

SELECT * FROM msdb.dbo.sysmail_allitems;

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.

I’m also displaying just the first row (even though four rows were returned), to keep things concise.

Here it is again, except this time I’ll display all four rows. This time I’ll switch to the normal horizontal output, and I’ll specify just a few columns.

SELECT
    mailitem_id,
    sent_status,
    send_request_date
FROM msdb.dbo.sysmail_allitems;

Result (using vertical output):

+---------------+---------------+-------------------------+
| mailitem_id   | sent_status   | send_request_date       |
|---------------+---------------+-------------------------|
| 1             | failed        | 2020-08-24 02:40:48.093 |
| 2             | failed        | 2020-08-24 02:47:40.833 |
| 3             | sent          | 2020-08-24 03:58:57.887 |
| 4             | sent          | 2020-08-24 04:11:19.300 |
+---------------+---------------+-------------------------+