Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

3 Answers 3

up vote 3 down vote accepted

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.

share|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. –  Jim 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 ;). –  Jim 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?

share|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. –  Jim 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
/*
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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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