locked
SQL Agent Job notification issue RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello,

    I am using SQL Server 2012 SE and have a sql agent job which deletes data from a few tables every night. The Job is set to send out notifications when it completes whether it fails or succeeds. We got emails everytime whether it failed or succeeded. However last week we noticed that we didnt receive our email notification after the job failed. After looking into the error log we came to know that the job failed due to a deadlock. But we should have received an email notification because the agent job result was still a failure.

    Has deadlock got anything to do with the email notification not being sent? The reason I am asking is we received our emails in the past when the job failed due to key violation issues.

    Please share your thoughts.

    • Moved by Olaf HelperMVP Sunday, February 19, 2017 7:09 AM Moved from "Database Design" to a more related forum
    Sunday, October 2, 2016 4:14 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote
    Thanks for the reply. I see row for that email in the msdb..sysmail_faileditems as well as msdb..sysmail_mailitems but nothing in sysmail_log.

    Sysmail_log -

    https://technet.microsoft.com/en-us/library/ms187540(v=sql.105).aspx

    Database Mail considers mail to be sent, when it is successfully delivered to a SMTP mail server. Subsequent errors, such as an invalid recipient e-mail address, can still prevent mail from being delivered, but will not be contained in the Database Mail log.

    Also,

    1.Do you see -was there any issues with SMTP server like n/w fluctuation or down or any other issues?.

    2.What is your SQL agnet job failures says in the log-can you paste here?.

    3.verify your db mail  & SQL agent service status?.

    4.Was there any recent changes made?

    Also-

    Troubleshooting Database Mail
    https://technet.microsoft.com/en-us/library/ms188663(v=sql.105).aspx

    Regards, S_NO "_"

    Monday, October 3, 2016 2:25 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi  ,

    use MSDB

    go

    SELECT * FROM sysmail_mailitems
    GO
    SELECT * FROM sysmail_log
    GO

    SELECT * FROM sysmail_sentitems

    GO

    SELECT * FROM sysmail_unsentitems

    GO

    SELECT * FROM sysmail_faileditems 

    GO

    share this result

    Please click Mark As Answer if my post helped.


    • Edited by AV111 Sunday, October 2, 2016 4:51 AM
    Sunday, October 2, 2016 4:47 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thanks for the reply. I see row for that email in the msdb..sysmail_faileditems as well as msdb..sysmail_mailitems but nothing in sysmail_log.
    Sunday, October 2, 2016 1:54 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thanks for the reply. I see row for that email in the msdb..sysmail_faileditems as well as msdb..sysmail_mailitems but nothing in sysmail_log.

    Sysmail_log -

    https://technet.microsoft.com/en-us/library/ms187540(v=sql.105).aspx

    Database Mail considers mail to be sent, when it is successfully delivered to a SMTP mail server. Subsequent errors, such as an invalid recipient e-mail address, can still prevent mail from being delivered, but will not be contained in the Database Mail log.

    Also,

    1.Do you see -was there any issues with SMTP server like n/w fluctuation or down or any other issues?.

    2.What is your SQL agnet job failures says in the log-can you paste here?.

    3.verify your db mail  & SQL agent service status?.

    4.Was there any recent changes made?

    Also-

    Troubleshooting Database Mail
    https://technet.microsoft.com/en-us/library/ms188663(v=sql.105).aspx

    Regards, S_NO "_"

    Monday, October 3, 2016 2:25 AM