sql agent auto restart

[zxmax]

Greeting, i'm new to ms sql, and i have a quick question
Is it possible to have the sql server agent to restart automatically if the sql server restart

lets say i want to restart the sql server, it automatically stops the server agent, then after restarting the sql server, the agent DOES NOT start automatically .. is it possible to have the agent to stat automatically >?

Thanks,

 

[tran008]

I think this can be done via the sql agent properties advanced tab from EM. Just check restart service box.

 

[JayKusch]

Tran008's suggestion may not work in all cases. Here is the way to make sure SQL Server Agent starts whenever the SQL Server services starts ...

One of the problems you may not be aware of when stopping and restarting SQL Server is that, by default, the SQL Server Agent WILL NOT restart when the SQL Server service starts up. This will cause any jobs you have schedule to not run ...

To fix this issue, run the following code in Query Analyzer on the Master DB, on any servers you plan to stop/start SQL Server.


CODE
CREATE PROC AutoStart_SQLServerAgent
AS
EXEC XP_CMDSHELL 'NET START SQLSERVERAGENT'
go
SP_PROCOPTION AutoStart_SQLServerAgent, STARTUP, TRUE

The code above will create the stored procedure AutoStart_SQLServerAgent in the Master DB.

The next command sets the stored procedure to run at the startup of the SQL Server service.

Enjoy!

Thanks

J. Kusch

 

[SonOfEmidec1100]

DejaVu thread962-868950

JayKush - your post looked much tidier there.

 

[JayKusch]

HAHA ... No Doubt! should have just pasted the link instead of the post itself! Ah ... live and learn.

Thanks

J. Kusch