{"id":9766,"date":"2020-09-19T08:49:18","date_gmt":"2020-09-18T22:49:18","guid":{"rendered":"https:\/\/database.guide\/?p=9766"},"modified":"2020-09-22T09:01:39","modified_gmt":"2020-09-21T23:01:39","slug":"check-unsent-mail-in-sql-server-t-sql","status":"publish","type":"post","link":"https:\/\/database.guide\/check-unsent-mail-in-sql-server-t-sql\/","title":{"rendered":"Check Unsent Mail in SQL Server (T-SQL)"},"content":{"rendered":"\n<p>When <a href=\"https:\/\/database.guide\/how-to-send-emails-from-sql-server-t-sql\/\" title=\"How to Send Emails from SQL Server (T-SQL)\">sending emails from SQL Server<\/a>, you can check for any unsent mail with the <code>sysmail_unsentitems<\/code> view.<\/p>\n\n\n\n<!--more-->\n\n\n\n<h2 class=\"wp-block-heading\">Example<\/h2>\n\n\n\n<p>Here&#8217;s an example of checking for unsent mail. Note that it needs to be run on the <code>msdb<\/code> database.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT * FROM msdb.dbo.sysmail_unsentitems;<\/code><\/pre>\n\n\n\n<p>Result (using vertical output):<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">mailitem_id &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | 4\nprofile_id&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | 1\nrecipients&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | homer@example.com\ncopy_recipients &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | NULL\nblind_copy_recipients &nbsp; &nbsp; &nbsp; | NULL\nsubject &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | SQL Server Agent Job: FAILED\nbody&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | Your favorite SQL Server Agent job just failed\nbody_format &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | TEXT\nimportance&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | NORMAL\nsensitivity &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | NORMAL\nfile_attachments&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | NULL\nattachment_encoding &nbsp; &nbsp; &nbsp; &nbsp; | MIME\nquery &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | NULL\nexecute_query_database&nbsp; &nbsp; &nbsp; | NULL\nattach_query_result_as_file | 0\nquery_result_header &nbsp; &nbsp; &nbsp; &nbsp; | 1\nquery_result_width&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | 256\nquery_result_separator&nbsp; &nbsp; &nbsp; | &nbsp;\nexclude_query_output&nbsp; &nbsp; &nbsp; &nbsp; | 0\nappend_query_error&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | 0\nsend_request_date &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | 2020-08-24 04:11:19.300\nsend_request_user &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | sa\nsent_account_id &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | NULL\nsent_status &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | unsent\nsent_date &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | NULL\nlast_mod_date &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | 2020-08-24 04:11:19.300\nlast_mod_user &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | sa<\/pre>\n\n\n\n<p>I used vertical output here so that you don&#8217;t need to scroll sideways to see all columns.<\/p>\n\n\n\n<p>In this case, there&#8217;s one unsent email. I happened to run this query immediately after executing <code>msdb.dbo.sp_send_dbmail<\/code> to send this mail. <\/p>\n\n\n\n<p>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).<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT * FROM msdb.dbo.sysmail_unsentitems;<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">(0 rows affected)<\/pre>\n\n\n\n<p>However, just because there are no unsent emails, it doesn&#8217;t mean that there are none that failed. <\/p>\n\n\n\n<p>You can query <code>sysmail_faileditems<\/code> to <a href=\"https:\/\/database.guide\/check-for-failed-email-in-sql-server-t-sql\/\" title=\"Check for Failed Email in SQL Server (T-SQL)\">return a list of failed emails<\/a>. <\/p>\n\n\n\n<p>You can also query <code>sysmail_sentitems<\/code> to <a href=\"https:\/\/database.guide\/return-a-list-of-emails-sent-from-sql-server-database-mail-t-sql\/\" title=\"Return a List of Emails Sent from SQL Server Database Mail (T-SQL)\">get all sent emails<\/a>.<\/p>\n\n\n\n<p>You can also query <code>sysmail_allitems<\/code> to <a href=\"https:\/\/database.guide\/check-the-status-of-all-database-mail-messages-in-sql-server-t-sql\/\">get all emails<\/a> (sent, unsent, failed, and retrying).<\/p>\n","protected":false},"excerpt":{"rendered":"<p>When sending emails from SQL Server, you can check for any unsent mail with the sysmail_unsentitems view.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[10,77,104,61],"class_list":["post-9766","post","type-post","status-publish","format-standard","hentry","category-sql-server","tag-how-to","tag-mssql","tag-send-email","tag-t-sql"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/9766","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=9766"}],"version-history":[{"count":10,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/9766\/revisions"}],"predecessor-version":[{"id":10068,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/9766\/revisions\/10068"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=9766"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=9766"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=9766"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}