Post reply

How can I relate retries with the original send in SQL Server Database Mail?

  • May 24, 2017 at 5:47 am

    #337231

    I'm using SQL Server Database Mail configured with 1 retry. The first send attempt produces a row in the msdb.dbo.sysmail_allitems view. If the first send attempt fails a second send attempt is made, which produces another row in that view. The mailitem_id of the two rows differs. Is there a way to correlate the two rows so that I can identify the retry row from the mailitem_id of the first send attempt?

  • May 25, 2017 at 1:00 pm

    #1944259

    chris-howard - Wednesday, May 24, 2017 5:47 AM

    I'm using SQL Server Database Mail configured with 1 retry. The first send attempt produces a row in the msdb.dbo.sysmail_allitems view. If the first send attempt fails a second send attempt is made, which produces another row in that view. The mailitem_id of the two rows differs. Is there a way to correlate the two rows so that I can identify the retry row from the mailitem_id of the first send attempt?

    Are you sure a new row is being added on the retry or after the retry is successful?
    Does the first attempt or first row have a status of failed? That would be a different mail item that failed after any retries. 
    You can also check sysmail_sentitems and sysmail_faileditems as well as the status column in sysmail_allitems to see what emails were sent and what emails failed. 

    Sue

  • May 25, 2017 at 2:36 pm

    #1944277

    i believe  msdb.dbo.sysmail_allitems will only have a single row per mail_id, along with it's current status (sent/unsent/failed)

    when a mail does fail, an entry is created in sysmail_event_log
    select
    mailitem_id,
    count(*)
    from msdb.dbo.sysmail_event_log
    group by mailitem_id
    having count(*) > 1;

    SELECT * FROM from msdb.dbo.sysmail_event_log

    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!

  • June 20, 2017 at 5:05 am

    #1947358

    Turns out I was wrong and the replies above are correct - sorry I wasted your time.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply