Auto-start SQLAgent service when SQLServer service starts? 1

[LFCfan]

Hi folks,

As far as I can tell from googling, while it is possible to set the SQLAgent service to start automatically after a server reboot, it doesn't appear that you can set it to start automatically when the SQLServer service starts (or restarts).

Is anyone able to confirm this?

We had an issue when the SQLSERVER service (and the Agent) was stopped, but the server guys neglected to restart the Agent. Cue jobs not having run this morning and mass panic...
The server guys are not SQL Server folk as our organisation is an Oracle house, so I guess wouldn't have thought to restart Sql Agent. It's only my department that uses Sql Server. I was hoping, besides making the server guys aware that SQLAgent needs to be running, to find a way to set it to start automatically after the Sql Server service is restarted, but I'm drawing a blank.

Many thanks.

~LFCfan

 

[mrdenny]

There is no way to force the service to restart when just the SQL Agent restarts.

The only way I can think to do this is to create a stored procedure such as this.

create procedure StartSQLAgent AS
exec xp_cmdshell 'net start SQLAgent'
go

Then mark the procedure to run at startup. This should start thw SQL Agent each time the SQL Server starts.

The better option would be to have some monitoring setup which will page the oncall person in the event that the SQL Agent isn't running. Most any enterprise monitoring solution can be used to do this.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[LFCfan]

Thanks a bunch Denny, and I didn't know about

use master
go
exec sp_procoption @procname = ‘proc1?, @optionname = ’startup’, @optionvalue = ‘on’
go

so thanks for that, too!

~LFCfan