none
Database Mail Fails For SQL Server Agent Jobs / Maintenace plans - Why ?

    Question

  • Database Mail Fails For SQL Server Agent Jobs / Maintenace plans - Why ?

    This has been a very common question I have seen relatedt to Database mail and I thought will post here so if you run into this issue, it could be due to one of the following and also have mentioned below how to get around them. Bottom line is datbase mail works fine with SQL Agent jobs and maintenace plans in SP1 on all platforms.

    1. If you see this on 64 bit platform and on RTM build, this is a bug that is addressed in SP1.

    2. If you see the below error message, it is known issue and this is because of design that requires SQL Agent to restart, if SQL Agent was started before enabling databasemail and setting a new profile. We are evaluating to change this design of not requiring to restart agent for our future release.

    Re: 264] An attempt was made to send an email when no email session has been established
    This problem will go away after restarting SQL Server Agent in this case. Thanks,

    Gops Dwarak, MSFT

    Wednesday, March 29, 2006 3:03 AM

Answers


  • I just had this problem and I was able to solve it. Thanks OP for starting this thread but I think the information here needs to be a little more detailed.

    I am on 2005 standard SP2. My servers have been in production for over 13 months with no database mail problems. I never had to check if the mail session is alive or not. System took care of that for me. Yesterday I started getting this error and SQL agent stopped sending me emails on jobs. Also jobs started failing with the error "domain\user is not valid...".  and I know that I didn't change a thing. I am not sure if we had an issue on the exchange/ windows side.

    So as a diehard MS fan I restarted the agent but that (unusual) didn't solve my problems. To solve this, I had to uncheck "Enable mail profile" on SQL Agent properties- Alert system. Then a stopped the agent. Started it. Enabled the database mail in the properties. Stop agent. Start agent.

    Now the agent started correctly and mails started coming in.

    Hope this helps someone.
    Tuesday, April 01, 2008 5:48 PM

All replies

  • Hello Gops..

    I get the same error :- [264] An attempt was made to send an email when no email session has been established.

    I tried restarting the SQL Agent as well as even the SQL Server Service. Still does not work. xp_sendmail works well. And the Database mail profile is declared as Public and Default. We are running on 64 bit Windows 2003 r2 with SP1 and 64 bit SQL 2005 Standard Edition Server with sp1.

    Any clue on what else to be done / checked??

    Regards

    N.Raja

    Tuesday, November 07, 2006 1:31 AM
  • Hello Gops,

    it is no solution to my problem. Do you have any ideas for my problem at

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=882761 ?

    Thanks
    tobias
    Tuesday, November 07, 2006 12:59 PM
  • Any updates on this? I'm having the same problem on a Windows 2003 with SQL 2005 SP1.

     

    Thanks,

    André

    Monday, February 19, 2007 2:16 AM
  • This may be related to the fact that DBMail, by default, shuts itself down after 10 minutes of inactivty.  If the mail service isn't running, you won't be able to queue up any mail messages.

     

    There may be a built-in mechanism for addressing this problem, but I found that I can detect this situation and start the mail listener before sending db mail.  I use the following in stored procs that need to send email.

     

    You could increase the inactivity period for db mail shutdown from 600 seconds to some larger value.  It might not make sense to do so, since shutting it down releases its resources back to teh system; if your SQL server only sneds mail infrequently, it might be better to start the mail listener on demand.  Someone with more experience than I might want to add their two cents' worth.  :-)

     

     

    /*********************************************************************/

    /* Check the mail engine. Start the service if is is stopped         */

    /* -- the DB Mail engine shuts down after 10 minutes (of inactivity) */

    /* -- this setting can be changed, but we probably don't need to.    */

    /*********************************************************************/

    DECLARE @rc INT

    IF NOT EXISTS (SELECT * FROM msdb.sys.service_queues

                   WHERE name = N'ExternalMailQueue' AND is_receive_enabled = 1)

        EXEC @rc = msdb.dbo.sysmail_start_sp

        -- TODO: add handling and logging for any engine start issues

     

     

    Saturday, June 30, 2007 7:18 PM
  • Thank you for this.. it is a very reasonable assertion.  I have a system that has jobs run on a nightly basis and is mostly inactive during the day.  I also have a alert set to send messages every evening as a failsafe to make sure that notifications are working.  It was a pain that the alert system would stop working after nearly a month of working, sending notifications every day.

    Tuesday, July 17, 2007 4:52 PM
  • Even with SP1 it doesnt work.  (I tried it on both the standard and Enterprise edition)

    Is there any other way of doing it without restarting SQL Agent?

    If we restart the agent, what other things will be affected by it? Will it affect replication jobs and other scheduled jobs?

     

    Thanks

    Wednesday, July 25, 2007 8:44 PM
  • I am in option 2 but restarting the SQL Server Agent doesn't help. I get error messages in the Agent log during start up

    1. [392] Using MAPI32.DLL from C:\WINDOWS\SYSTEM32 (version 1.0.2536.0)
    2. [196] Attempting to start mail session using profile ‘agentJobs’...
    3. [260] Unable to start mail session (reason: Unable to logon (reason: MAPI Logon failed); check the mail profile and/or the SQLSERVERAGENT service startup account)

    I don't understand why is trying a MAPI Logon when Database mail doesn't use MAPI (one of its theoretical strengths)

    Any help welcomed
    Thursday, August 30, 2007 11:52 AM
  • Do not forget, as I had, that you need to reach Agent Properties, Alerts, Mail Session, Enable Mail Profile check box with selection setup. Restarting will not help you until you get this setup right.

    Wednesday, October 17, 2007 6:48 PM
  • I have restarted the SQL agent. The email notifcation on the server is infrequent, how can I start the email sesssion whenever I need to send out notification via database mail?

     

     

     

    Monday, January 07, 2008 11:29 AM

  • I just had this problem and I was able to solve it. Thanks OP for starting this thread but I think the information here needs to be a little more detailed.

    I am on 2005 standard SP2. My servers have been in production for over 13 months with no database mail problems. I never had to check if the mail session is alive or not. System took care of that for me. Yesterday I started getting this error and SQL agent stopped sending me emails on jobs. Also jobs started failing with the error "domain\user is not valid...".  and I know that I didn't change a thing. I am not sure if we had an issue on the exchange/ windows side.

    So as a diehard MS fan I restarted the agent but that (unusual) didn't solve my problems. To solve this, I had to uncheck "Enable mail profile" on SQL Agent properties- Alert system. Then a stopped the agent. Started it. Enabled the database mail in the properties. Stop agent. Start agent.

    Now the agent started correctly and mails started coming in.

    Hope this helps someone.
    Tuesday, April 01, 2008 5:48 PM
  • The restart of the SQL Server Agent WORKED.  Thank you very much.

    Gary
    Tuesday, August 26, 2008 8:38 PM
  • I encountered the same problem and followed the steps

    outlined in the post. It worked like a charm, problem solved.

    Many thanks for posting the solution. It saved me time.

    Tuesday, September 02, 2008 1:06 PM
  •  JoeTig wrote:
    I encountered the same problem and followed the steps

    outlined in the post. It worked like a charm, problem solved.

    Many thanks for posting the solution. It saved me time.

     

    Awesome. Please rate yes to the post that helped you

    Tuesday, September 02, 2008 5:38 PM
  • this worked for me too in SQL 2008 having the same problems as described above, restart the SQL agent and then toggled the Enable mail profile checkbox (even though the test button is disabled) I was soon seeing opertors being emailed once SQL Agent jobs had completed which was never happening before although DBMail was firing emails correctly, thanks for the help guys.

     

    Wednesday, November 19, 2008 12:07 PM
  • work !! thanks...

     

    Yompol

     

    Tuesday, December 09, 2008 2:42 PM
  • Followed Arun21 and all is fine.  I am also on 2005 standard SP2
    Monday, March 02, 2009 2:46 PM
  • Hi there,

    I have tried all the proposed solutions on this issue, unfortunately none of them did the trick.

    Our System:
    We are running a Win2003 Std 64x with SP2
    SQL Server 2005 Enterprise 64x (latest SP's)
    2 Instanses: Default (MSSQLSERVER) and a named one.

    The send "notification email when job fails" works perfectly on the default instance, but not on the named one.
    I have really tried everything that was suggested from restarting the SQL Agent Service (both instances), restarting the server, recreating multi operators.
    I also set the SQL Agent to use the db mail profile for alerts.

    Although when I run the sp_send_dbmail it does work fine, but not through the job.

    Your help will be really appreciated with this,

    Thanks very much
    Friday, April 17, 2009 9:38 AM
  • Please check this out - it worked wonder for me and I am so glad it got resolved - many thanks.

    "So as a diehard MS fan I restarted the agent but that (unusual) didn't solve my problems. To solve this, I had to uncheck "Enable mail profile" on SQL Agent properties- Alert system. Then a stopped the agent. Started it. Enabled the database mail in the properties. Stop agent. Start agent."

    Awesome!!
    Monday, June 01, 2009 1:05 PM
  • Thanks "Arun21".....
    now it works ;-)
    Friday, October 02, 2009 2:15 PM
  • For us, we had to enable mail profile in ALERTS within the properties of Database Mail.  We also had to start the messenger service.
    Saturday, October 17, 2009 3:51 AM
  • Special thanks! It was very helpfull!!!
    Monday, December 07, 2009 1:21 PM
  • Thanks Devilz666 for your suggestion.  That did the trick for me!!
    Tuesday, January 19, 2010 4:22 PM
  • 1. Please check if the 'Enable mail profile' at properties of the sqlserver agent alert system is checked

    2. stop the Agent and start it again

    It should work now

    • Proposed as answer by Ajay.G Friday, March 30, 2012 6:57 AM
    Friday, June 18, 2010 10:08 AM
  • Thanks! it was very helpful.
    • Proposed as answer by Ajay.G Friday, March 30, 2012 6:56 AM
    Wednesday, June 30, 2010 9:16 PM
  • thanks a lot, this solved my problem as well.
    Monday, March 21, 2011 12:04 PM
  • Thank you - I was having this issue and applied your workaround and now mail is working again.

    Colleen


    DBA

    Tuesday, June 19, 2012 4:02 PM
  • Thanks, solved my issue too.
    Thursday, July 19, 2012 5:18 AM
  • This is such nonsense from Microsoft! First of all, I thank the original poster to providing this solution as this was in fact what I needed to do! This solution was for 2005 SP2, but this solution also has to be done for this behavior  SQL Server 2008 R2. Microsoft needs to fix this so that it is not so cumbersome! Arun21 is the man!
    • Edited by CG_NJ Thursday, October 03, 2013 8:27 PM
    Thursday, October 03, 2013 8:26 PM
  • The error I encountered was that in setting up the operator, "Email Name" is not inf act the Name but email address.
    Monday, August 25, 2014 5:18 PM