Every now and again you need to run code when your instance starts up. For example you might want to check if the SQL Agent is running.

Startup Procedures

The first thing you have to do is make sure that the scan for startup procs configuration is set to 1.

EXEC sp_configure 'scan for startup procs'

And if it isn’t then we turn it on.

EXEC sp_configure 'scan for startup procs', 1

Output:

Configuration option ‘scan for startup procs’ changed from 0 to 1. Run the RECONFIGURE statement to install.

There is no reason to run RECONFIGURE since it won’t be used until after the next reboot anyway. Now that the system will actually use our startup SP we can create the SP we want and mark it to be run on startup.

I’m using some code of Aaron Bertrand’s (b/t) that I found in this question on Stack Overflow and modified it to suit my needs here.

CREATE PROCEDURE sp_AgentStartup AS
DECLARE @agent NVARCHAR(512);
SELECT @agent = COALESCE(N'SQLAgent$' + CONVERT(SYSNAME, SERVERPROPERTY('InstanceName')), 
  N'SQLServerAgent');
CREATE TABLE #QueryState (CurrentServiceState varchar(20))
INSERT INTO #QueryState
EXEC master.dbo.xp_servicecontrol 'QueryState', @agent;
IF EXISTS (SELECT 1 FROM #QueryState WHERE CurrentServiceState = 'Stopped.')
EXEC master.dbo.xp_servicecontrol 'Start', @agent;
GO
EXEC sp_procoption 'sp_AgentStartup','startup','on'
GO

sp_procoption is a system stored procedure that lets us change the options on a stored procedure and in this case set it to run on startup. Note: The account that is running SQL Server needs to have permissions to start the Agent service. (Or do whatever your startup stored procedure does.) You can have as many stored procedures running on startup as you want but remember the more you have the longer it’s going to take for your instance to start.

Run job on startup of the Agent

Ok, now that the Agent is started, if we have something more complicated we want to run, we can tell a job to run on startup. (Startup of the Agent.) It’s really pretty easy though. It’s just a matter of creating a schedule with a schedule type of Start automatically when SQL Server Agent starts.

Filed under: Microsoft SQL Server, Settings, SQL Services, SQLServerPedia Syndication, System Functions and Stored Procedures Tagged: instance settings, microsoft sql server, SQL Agent Jobs, system functions