Database Mail Fails For SQL Server Agent Jobs / Maintenace plans - Why ?
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
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
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
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
