Binaryworld - How to start SQL Server Agent automatically when SQL Server starts. ... [ SQL Server -> Server Operations ]

When you install SQL Server by default SQL Server Agent Service is set to Start Manual mode and some times this can stop your scheduled jobs. Its also possible that you stop Agent and forget to turn it on.

This article will show you how to create a SP which can start SQL Server Agent service if its not running. You can use sp_procoption SP to put any SP in the startup list so everytime SQL Server starts SP gets executed.

Click here to copy the following block

Use Master

Go

/*
Description : By default SQL Server Agent is not in Autostart mode. This stored procedure will insure that Agent starts when you start your sql server
*/
Create PROC usp_AutoStart_SQLAgent
AS
BEGIN
DECLARE @Err int, @Msg varchar(100), @ServiceName sysname
Declare @state varchar(1000)

SET @ServiceName='SQLSERVERAGENT' --// for named instance this sud be SQLSERVERAGENT$YourInstanceName

create table #t1(fld varchar(1000))

insert into #t1
exec xp_servicecontrol QueryState, @servicename

select top 1 @state=fld from #t1

Print 'Before : State of ' + @servicename + ' is ' + @state
truncate table #t1

if @state<>'Running.'
begin
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

insert into #t1
exec xp_servicecontrol QueryState, @servicename

select top 1 @state=fld from #t1

Print 'After : State of ' + @servicename + ' is ' + @state
END

GO

--//Put this usp_AutoStart_SQLAgent SP in startup list so everytime you start sql server, SQL Agent also starts
EXEC sp_procoption 'usp_AutoStart_SQLAgent', 'startup', 'true'
GO

--//Lets start Agent service if not running
exec usp_AutoStart_SQLAgent
GO