Auto Start the SQL Server Agent Service

You can ensure that your SQl Server Agent always restarts when the SQL Server restarts by using sp_Configure and xp_cmdshell, as shown underneath:

1. Enabe 'xp_cmdshell'

-- Allow advanced options to be changed.
EXEC Sp_configure 'show advanced options', 1;
go
-- Update currently configured value for advanced options.
RECONFIGURE;
go
-- Enable
EXEC Sp_configure 'xp_cmdshell', 1;
go
-- Update value.
RECONFIGURE;
go

2. Create a stored procedure to start SQL Server Agent

CREATE PROCEDURE StartAgent
AS
EXEC Xp_cmdshell
'NET START SQLSERVERAGENT'

3. Configure stored procedure as an auto-start stored procedure.

EXEC Sp_procoption StartAgent, startup, true