The second set of
msdb objects (and perhaps the more important ones) we’ll review are those used to discover the status of mail messages.
The first thing you need to do is to check on the status of the mail messages you’ve attempted to send, without relying on inboxes to tell you if they’ve been received. Several views in
msdb enable this, most of which may be filtered by mail account, sending user, send date, status, and more. To begin this process, you query the view
sysmail_allitems, which contains all the data about your messages (subjects, recipients, importance, and so on) as well as
sent_status. Here’s an example:
SELECT mailitem_id, subject, sent_status ...