dcsimg
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL
Expertise: Intermediate
Dec 28, 2018

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
Hannes du Preez
 
Thanks for your registration, follow us on our social networks to keep up-to-date