SQLSERVERAGENT

[rhysmeister]

Upon a reboot the SQL Server Agent service fails to start. The error reported is that the service is timing out before it is able to start. I am able to stop and start the SQLSERVERAGENT service with no problems once the server is up and running.

I have set the service to restart if it fails but it doesn't start once the server is up (automatically). I guess this is because it can't "restart" a service that has never actually started.

Is there a way to increase the timeout of this service with startup parameters?

 

[JayKusch]

If you are trying to have the SQL Server Agent restart each time the SQL Server service starts, you need to create the stored procedure below. The code will also set the SP as an SP that needs to be started each time the SQL Server service is started.

USE Master
GO

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

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

Thanks

J. Kusch

 

[rhysmeister]

Thanks, I'll give it a go.