sysmail_allitems (Transact-SQL)

Applies to: SQL Server Azure SQL Managed Instance

Contains one row for each message processed by Database Mail. Use this view when you want to see the status of all messages.

To see only messages with the failed status, use sysmail_faileditems. To see only unsent messages, use sysmail_unsentitems. To see only messages that were sent, use sysmail_sentitems.

Column name Data type Description
mailitem_id int Identifier of the mail item in the mail queue.
profile_id int The identifier of the profile used to send the message.
recipients varchar(max) The e-mail addresses of the message recipients.
copy_recipients varchar(max) The e-mail addresses of the recipients who receive copies of the message.
blind_copy_recipients varchar(max) The e-mail addresses of recipients who receive copies of the message, but whose names don't appear in the message header.
subject nvarchar(510) The subject line of the message.
body varchar(max) The body of the message.
body_format varchar(20) The body format of the message. The possible values are TEXT and HTML.
importance varchar(6) The importance parameter of the message.
sensitivity varchar(12) The sensitivity parameter of the message.
file_attachments varchar(max) A semicolon-delimited list of file names attached to the e-mail message.
attachment_encoding varchar(20) The type of mail attachment.
query varchar(max) The query executed by the mail program.
execute_query_database sysname The database context within which the mail program executed the query.
attach_query_result_as_file bit When the value is 0, the query results were included in the body of the e-mail message, after the contents of the body. When the value is 1, the results were returned as an attachment.
query_result_header bit When the value is 1, query results contained column headers. When the value is 0, query results didn't include column headers.
query_result_width int The query_result_width parameter of the message.
query_result_separator char(1) The character used to separate columns in the query output.
exclude_query_output bit The exclude_query_output parameter of the message. For more information, see sp_send_dbmail.
append_query_error bit The append_query_error parameter of the message. 0 indicates that Database Mail shouldn't send the e-mail message if there's an error in the query.
send_request_date datetime The date and time the message was placed on the mail queue.
send_request_user sysname The user who submitted the message. This value is the user context of the database mail procedure, not the From: field of the message.
sent_account_id int The identifier of the Database Mail account used to send the message.
sent_status varchar(8) The status of the mail. Possible values are:

sent - The mail was sent.
unsent - Database mail is still attempting to send the message.
retrying - Database Mail failed to send the message but is attempting to send it again.
failed - Database mail was unable to send the message.
sent_date datetime The date and time that the message was sent.
last_mod_date datetime The date and time of the last modification of the row.
last_mod_user sysname The user who last modified the row.

Remarks

Use the sysmail_allitems view to see the status of all messages processed by Database Mail. This view can help you identify the nature of any problems, by showing you the attributes of the messages that were sent compared with the attributes of the messages that weren't sent.

The system tables exposed by this view contain all messages and might cause the msdb database to grow. Delete old messages from the view periodically to reduce the size of the tables. For more information, see Create a SQL Server Agent Job to Archive Database Mail Messages and Event Logs.

Permissions

Granted to sysadmin fixed server role and DatabaseMailUserRole database role. When executed by a member of the sysadmin fixed server role, this view shows all messages. All other users only see the messages that they submitted.