Running code on the startup of SQL Server

1

February 1, 2016 by Kenneth Fisher

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'

StartupCode

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.

StartupCode2

One thought on “Running code on the startup of SQL Server

  1. […] Kenneth Fisher describes startup stored procedures: […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,146 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: