SQL Agent automatic restart 1

[snarky]

I'm running SQL 2000. I want SQL Agent to restart automatically when SQL itself restarts.

I thought that checking off the box 'Auto Restart SQL Server Agent if it stops unexpectedly' (SQL Agent > Properties > Advanced tab) would do it but it doesn't appear to be working. If I stop the server and start it up again, SQL Agent doesn't start up again (it was running just prior to me stopping SQL server). I know that I can use Windows Services but I thought that would only take effect once the server (OS) is rebooted. I want it to come back regardless of how the server is stopped.

Can anyone shed some light on this one for me? Thanks in advance for any help you can provide.

 

[JayKusch]

Open your SQL Server Service Manager.

Make sure the SQL Server Agent Service has the
Auto-start service when OS Starts check box checked too.

Thanks

J. Kusch

 

[snarky]

Hey JayKusch -

Thanks for the quick reply. I checked off that box and tested it again - still doesn't work. Here's what I'm doing to test:

- Make sure all 'autostarts' are checked off (SQL server properties, SQL Agent properties, Services, etc).
- Make sure SQL Agent is running.
- Go to the Services Manager and stop SQL Server.
- Go to the Services Manager and start SQL Server.

I want to see SQL Agent come back on automatically, but it doesn't. There's no reboot involved here.

Any more ideas?

 

[JayKusch]

When you say "checked off" you mean you are enabling the option by checking the box ... True?

Thanks

J. Kusch

 

[snarky]

Yes, I'm enabling the autostart options. Sorry for the vagueness there.

 

[JayKusch]

This may help ... Run it in Query Analyzer against the Master DB. Make sure 'scan for startup procs' option is set at 1. can check that by running sp_configure.

CREATE PROC AutoStart_SQLAgent
AS
BEGIN
 DECLARE @Err int, @Msg varchar(100), @ServiceName sysname

 SET @ServiceName = 'SQLServerAgent'

 EXEC master.dbo.xp_servicecontrol 'START', @ServiceName 
  SET @Err = @@ERROR
  IF @Err = 0
    BEGIN
      RAISERROR ('Successfully started SQL Server Agent', 1, 1) WITH LOG
    END
   ELSE
    BEGIN
     SET @Msg = 'Error occured while starting SQL Server Agent. Error code: ' + STR(@Err)
			RAISERROR (@Msg, 18, 1) WITH LOG
    END
END
GO

EXEC sp_procoption 'AutoStart_SQLAgent', 'startup', 'true'
GO

Thanks

J. Kusch

 

[JayKusch]

Snarky ...

Did this work for you?

Thanks

J. Kusch

 

[snarky]

I'm going to try it this morning. I'll keep you posted!

 

[snarky]

Worked like a charm! Thanks for your help Jay!