dcsimg
 

Submitted by: Hannes du Preez(ojdupreez1978@gmail.com)

Friday Dec 28th 2018 by Hannes du Preez

Language: SQL, Expertise: Intermediate - Learn how to ensure that your SQL Server Agent always restarts when the SQL Server restarts.

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
Home
Mobile Site | Full Site