1

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.

3 Answers 3

2

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.

6
  • 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, 2011 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.
    – user28664
    May 25, 2011 at 14:53
  • if you do it out of hours make sure its not in the process of doing any tasks.
    – beakersoft
    May 25, 2011 at 14:55
  • I'll give you the check mark if I still have my job tomorrow ;).
    – user28664
    May 25, 2011 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, 2011 at 18:39
0

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?

1
  • Just the error that there is no mail session established. There is some info on other forums that restarting the agent will work.
    – user28664
    May 25, 2011 at 14:54
0

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

List Running Jobs
/*
DESCRIPTION:
List the RUNNING jobs on an instance

CONFIGURATION
None

Compatibility list:
MSSQL2005
MSSQL2008
Does not work
MSSQL2000

Legend
@execution_status=1 Running
@execution_status=4 Idle
*/


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

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

SELECT
-- [job_id]
[originating_server],[name],[enabled]
--,[description]
,[start_step_id]
--,[category]
,[owner]
--,[notify_level_eventlog],[notify_level_email],[notify_level_netsend],[notify_level_page],[notify_email_operator],[notify_netsend_operator],[notify_page_operator],[delete_level],[date_created],[date_modified]
--,[version_number]
,[last_run_date]
,[last_run_time]
,[last_run_outcome]
,[next_run_date]
,[next_run_time]
--,[next_run_schedule_id]
,[current_execution_status]
,[current_execution_step]
,[current_retry_attempt]
--,[has_step],[has_schedule],[has_target]
,[type]
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
GO
sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

--OUTPUT

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.

You must log in to answer this question.