I've found myself in the tenuous role of a developer tasked to manage a SQL server. I've set up some jobs to run on a schedule, but would like to receive email notification on completion.

I've set up Database Mail and successfully sent a test message but I receive an error saying that an attempt was made to send mail when no session was established. In researching this there is a lot of noise that restarting the SQL Server Agent will fix the problem.

But before I go too far down the rabbit hole, I thought I'd ask if there are any unintended consequences of restarting the SQL Server Agent? For example, will it affect any current jobs, or is there a high risk that it will shut down but won't restart?

This is SQL 2005 x64 SP4.


The agent only tends to deal with running maintenance jobs (backups, re-indexing etc). Check there will be nothing running when before you restart it and you should be ok. I've never not had it restart before, but like anything else it i'd do it out of hours if your worried.

| improve this answer | |
  • I have never had issues restarting this service as well. To be 100% safe, I would just check that there are no running jobs at the time you restart the service. – Lucky Luke May 25 '11 at 14:51
  • I think that's exactly it. I just need to do it and see, but out of hours is key. It seems like a normal task, but as a newbie I'm terrified of hosing something. – plntxt May 25 '11 at 14:53
  • if you do it out of hours make sure its not in the process of doing any tasks. – beakersoft May 25 '11 at 14:55
  • I'll give you the check mark if I still have my job tomorrow ;). – plntxt May 25 '11 at 14:56
  • 1
    You'll also want to verify if any jobs are scheduled to "Start automatically when SQL Server Agent starts". These jobs will be run if you restart SQL Server Agent. – db2 May 25 '11 at 18:39

From my experience with SQL, you should be able to restart this service without any unintended consequences. As long as SQL Server is running, there shouldn't be any issues with the Agent service coming back up once you stop it.

What did you see in the event logs that makes you think it needs a restart?

| improve this answer | |
  • Just the error that there is no mail session established. There is some info on other forums that restarting the agent will work. – plntxt May 25 '11 at 14:54

Run the script to find out if there is any job running. If not then is safe to restart it.

List Running Jobs
List the RUNNING jobs on an instance


Compatibility list:
Does not work

@execution_status=1 Running
@execution_status=4 Idle

IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#JobInfo'))

FROM OPENROWSET('sqloledb', 'server=(local);trusted_connection=yes'
, 'set fmtonly off exec msdb.dbo.sp_help_job @execution_status=1')

-- [job_id]
FROM #JobInfo

When you get an error, below is the solution

Resolution: Basically, ‘Ad hoc Distributed Queries’ are disabled by default in SQL Server due to security configuration and you cannot use either OPENROWSET or OPENDATASOURCE and if you cannot execute these two rowset functions, then you cannot access any remote data sources. So how to fix this issue? Given below is the script to enable ‘Ad hoc Distributed Queries’.

USE master
sp_configure 'show advanced options', 1;
sp_configure 'Ad Hoc Distributed Queries', 1;


Configuration option ‘show advanced options’ changed from 0 to 1. Run the RECONFIGURE statement to install. Configuration option ‘Ad hoc Distributed Queries’ changed from 0 to 1. Run the RECONFIGURE statement to install.

As you can see above, the ‘Ad hoc Distributed Queries’ setting changed from 0 to 1. Now you can easily execute any ‘Ad hoc Query’

Note : By default, this option is set to 0 and you need to change it to 1 to activate this feature.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.