Is sp_send_dbmail Asynchronous?

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  January 24, 2019 at 8:28 am

  #2019970

  mhtanner - Thursday, January 24, 2019 1:12 AM

  db_send_dbmail returns a mailitem_id immediately (the email is queued to be sent later). Use that to read msdb.dbo.sysmail_mailitems and check sent_status at a later time (values 0-3 in my previous posting), rather than worrying about a unique subject.  On our system it usually sends (or fails) within a second or two, but when there are problems can take up to a couple of minutes.

  Exactly what I've usually seen as well. The optional output parameter of @mailitem_id from the stored procedure tends to get overlooked.

  Sue

• pietlinden

  SSC Guru

  Points: 63557

  More actions

  January 24, 2019 at 9:07 am

  #2019982

  Okay, thanks!  (Finally got the e-mail from my database!... where do they go when they get lost between my computer and gmail?) So I know the code works...
  Guess I'll go play with it some more.  But I think my problem is solved for now.

• Lowell

  SSC Guru

  Points: 323518

  More actions

  January 24, 2019 at 9:19 am

  #2019990

  here's a  scripts i actually put into views ie (GoodMail and FailMail) by toggling the WHERE statement; very handy for at a glance status of emails.
  so i can review recent emails:
  
SELECT top 100
mail.send_request_date As SentDate,
sent_status As Reason,
err.[description],
mail.*
FROM [msdb].[dbo].[sysmail_allitems] mail
inner join [msdb].[dbo].[sysmail_event_log] err
  ON err.mailitem_id = mail.mailitem_id
WHERE mail.sent_status <> 'sent'
  order by mailitem_id desc


  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Chris Harshman

  SSC-Forever

  Points: 42192

  More actions

  January 24, 2019 at 10:39 am

  #2020010

  In addition to Lowell's query showing you the details of messages that have errored, there is also a query you can run to see items that are stuck in an UNSENT state, and wouldn't show up in sysmail_event_log:
  SELECT * FROM dbo.sysmail_unsentitems;
  Note: this does just query sysmail_allitems behind the scenes.