locked
SQL server Agent Job email notifications not being sent RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    I trying to set up some failure job Email notifications on Sql Server Agent jobs. 
    So, I configured database mail and tested and was able to receive mail through sp_send_dbmail and "Test mail"
    option under database mail.
     
    I enabled the mail session on sql server Agent and selected DatabaseMail and <<ProfileName>> for Mail system and Mail Profile
    options and restarted the sql server agent. But still I do not receive email notifications. 
    also, the operators are configured fine and have correct email id assigned.

    below is what I see in the agent error log after I restarting
    Message

    [355] The mail system failed to initialize; check configuration settings
    [264] An attempt was made to send an email when no email session has been established

    Do not know why it says no email session has been establised. SQL Server Version : Sql server 2005 standard edition SP3(x64).
    Also, I restarted Sql server agent several times. so, I think that's not an issue.

    again, I can receive mails when I do sp_send_dbmail but it is only issue sql server agent mail notifications.

    Please advice.

    Hope it Helps!!

    Thursday, February 14, 2013 9:37 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Stan210,

    Please run the following command first and check whether the notification mail was generated, its body is something like:

    JOB RUN: 'xxxxxx' was run on 2013/2/19 at 10:46:39 DURATION: 0 hours, 0 minutes, 0 seconds STATUS:  Failed MESSAGES: The job failed.  The Job was invoked by User xxxxx.  xxx.

    use msdb;
go


SELECT *
FROM sysmail_allitems;
GO

    If the notification mail was not generated, this issue can occur if notification mail profile has not been set yet. Please open SQL Server Agent properties, click “Alert System” tab, check “Enable mail profile” option, choose “Mail system” as “Database Mail”, and choose the correct profile. After this, please restart SQL Server Agent and run the job again.

    And then, please run the following command to get more detail information about failed mail :

    use msdb;
go

SELECT *
FROM sysmail_log
GO


    Allen Li
    TechNet Community Support

    • Proposed as answer by DaveHark Tuesday, February 6, 2018 7:28 PM
    • Marked as answer by Olaf HelperMVP Saturday, November 10, 2018 8:04 AM
    Tuesday, February 19, 2013 2:58 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Stan,

    Please make sure mail profile you configured is public and sqlagent service account has access to it.

    (Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker)

    Thanks,

    Atul Gaikwad.

    Friday, February 15, 2013 12:03 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    profile setting may not be refreshed by SQL Agent . Did you re-start sql agent?

    Let us know if you could upgrade to SQL 2012, In SQL 2012, mail profile settings  are automatically updated without the need to restart SQL Agent

    Thanks, Sethu Srinivasan [MSFT] SQL Server http://blogs.msdn.com/sqlagent -------------------------------------------------------------------------------- This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Friday, February 15, 2013 6:32 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Did you try test mail before trying it for the job failure alert? If you are not getting the test mail there should be a problem with the DBMail configuration.

    Manohar Punna | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog

    Friday, February 15, 2013 7:21 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello all,

    yes, I did restart the sql server agent several times. also, the profile I configured for sql server agent is the 

    default profile for database mail. I can get the mails from sp_send_dbmail and I get the test mail from database mail configuration.

    I am not able to receive mails from sql server agent .

    Again, the profile being used is default public profile.

    I enabled mail on sql server agent properties I selected database mail in "mail system" and myprofileemail(which is default public profile) to be used.
    why is the "Test" button in Alert System  in Sql server agent Grayed out??
    Server is sql server 2005 standard edition sp3

    Hope it Helps!!

    Friday, February 15, 2013 2:58 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    hi check below tables to see if the agemt really sent the mail or not

    USE MSDB

    select * from sysmail_faileditems

    select * from sysmail_sentitems


    select * from sysmail_allitems

    Friday, February 15, 2013 3:14 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    no, it the mail is not being sent. this is what I see in the agent error log 

    Message
    [264] An attempt was made to send an email when no email session has been established.

    sp_send_dbmail works fine it is only the agent mail . i restarted agent several times.

    Hope it Helps!!

    Friday, February 15, 2013 3:23 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Stan210,

    Please run the following command first and check whether the notification mail was generated, its body is something like:

    JOB RUN: 'xxxxxx' was run on 2013/2/19 at 10:46:39 DURATION: 0 hours, 0 minutes, 0 seconds STATUS:  Failed MESSAGES: The job failed.  The Job was invoked by User xxxxx.  xxx.

    use msdb;
go


SELECT *
FROM sysmail_allitems;
GO

    If the notification mail was not generated, this issue can occur if notification mail profile has not been set yet. Please open SQL Server Agent properties, click “Alert System” tab, check “Enable mail profile” option, choose “Mail system” as “Database Mail”, and choose the correct profile. After this, please restart SQL Server Agent and run the job again.

    And then, please run the following command to get more detail information about failed mail :

    use msdb;
go

SELECT *
FROM sysmail_log
GO


    Allen Li
    TechNet Community Support

    • Proposed as answer by DaveHark Tuesday, February 6, 2018 7:28 PM
    • Marked as answer by Olaf HelperMVP Saturday, November 10, 2018 8:04 AM
    Tuesday, February 19, 2013 2:58 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Allen,

    The notification is not being generated. YES, I have enabled the mail profile for sql serve agent and restarted sql server agent.

    the mail profile configured is public and available to all users.  sp_senddbmail works fine. we are sql 2005 sp3. 

    When the sql server agent tries to send mail, I this message in the error log.

    "[264] An attempt was made to send an email when no email session has been established "

    Right after restarting the sql server agent , I see

    [264] An attempt was made to send an email when no email session has been established

    There are these other errors like

    [LOG] Unable to read local eventlog (reason: The parameter is incorrect)... any other suggestions??

    Hope it Helps!!

    Thursday, February 21, 2013 11:45 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    http://connect.microsoft.com/SQLServer/feedback/details/125887/unable-to-read-local-eventlog-reason-the-parameter-is-incorrect

    have you installed sp3? please double check

    Wednesday, February 27, 2013 8:04 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thank you!
    Tuesday, February 6, 2018 7:28 PM