Automatically Running Stored Procedures at SQL Server Startup

By: Armando Prato   |   Comments (4)   |   Related: > Stored Procedures

Problem

I have a stored procedure I want to run when SQL Server starts. Is there a way to execute this procedure automatically each time the SQL Server service is started?

Solution

SQL Server offers the system stored procedure sp_procoption which can be used to designate one or more stored procedures to automatically execute when the SQL Server service is started. This is a handy option that can be leveraged for a variety of uses. For instance, you may have an expensive query in your database which takes some time to run at first execution. Using sp_procoption, you could run this query at server startup to pre-compile the execution plan so one of your users does not become the unfortunate soul of being first to run this particular query. I've used this feature to set up the automatic execution of a Profiler server side trace which I've scripted. The scripted trace was made part of a stored procedure that was set to auto execute at server start up.

exec sp_procoption @ProcName = ['stored procedure name'], 
@OptionName = 'STARTUP', 
@OptionValue = [on|off]

Here is an explanation of its parameters:

• Parameter @ProcName is self explanatory; it's the name of the procedure marked for auto-execution
• Parameter @OptionName is the option to use. The only valid option is STARTUP
• Parameter @OptionValue toggles the auto-execution on and off

Using sp_procoption comes with certain restrictions:

• You must be logged in as a sysadmin to use sp_procoption
• You can only designate standard stored procedures, extended stored procedures, or CLR stored procedures for startup
• The stored procedure must be located in the master database
• The stored procedure must not require any input parameters or return any output parameters

In the following example, I create a stored procedure that will be automatically run everytime my SQL Server instance starts. The purpose of this procedure is to write a row to a database table that logs the service start-up time. Using this table, I can get an idea of server up-time. The following script creates a new database that stores a metric table called SERVER_STARTUP_LOG. This table will hold the date and time the server was last started up. Once this infrastructure is built, I create the stored procedure that will be used to INSERT into this table at server startup. Note that the procedure is created in the master database.

USE MASTER
GO

CREATE DATABASE SERVER_METRICS
GO

USE SERVER_METRICS
GO

CREATE TABLE DBO.SERVER_STARTUP_LOG
(
LOGID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
START_TIME DATETIME NOT NULL
CONSTRAINT DF_START_TIME DEFAULT GETDATE() 
)
GO 

USE MASTER
GO

CREATE PROCEDURE DBO.LOG_SERVER_START
AS
SET NOCOUNT ON
PRINT '*** LOGGING SERVER STARTUP TIME ***'
INSERT INTO SERVER_METRICS.DBO.SERVER_STARTUP_LOG DEFAULT VALUES
GO

Now that the necessary objects have been built, we need to mark the created procedure to automatically start up when the server starts up. Running the following query, we can see that the sp_configure advanced option 'scan for startup procs' needs to be set. There is no need to do it manually; running sp_procoption will automatically set it for you.

USE MASTER
GO
SELECT VALUE, VALUE_IN_USE, DESCRIPTION 
FROM SYS.CONFIGURATIONS 
WHERE NAME = 'scan for startup procs'
GO

We can now use sp_procoption to mark the procedure for auto-execution

USE MASTER
GO
EXEC SP_PROCOPTION LOG_SERVER_START, 'STARTUP', 'ON'
GO

Re-running our configuration check, we now see that the server is configured to check for startup procedures (VALUE = 1) but the running value currently in effect is still set to not check for startup procedures (VALUE_IN_USE = 0). We'll need to re-start the SQL Server service to have the change take effect.

USE MASTER
GO
SELECT VALUE, VALUE_IN_USE, DESCRIPTION 
FROM SYS.CONFIGURATIONS 
WHERE NAME = 'scan for startup procs'
GO

If we re-start the SQL Server service, we see that the configuration value now takes effect

Furthermore, examining the previously created SERVER_STARTUP_LOG table, we see that the server startup time has been logged to the table

USE SERVER_METRICS
GO
SELECT * FROM SERVER_STARTUP_LOG
GO

Lastly, examining the SQL Server error log also verifies the procedure has been automatically run.

USE MASTER
GO
EXEC XP_READERRORLOG
GO

Now let's turn the auto-execution off. Once set off, the procedure will not run the next time SQL Server starts.

USE MASTER
GO
EXEC SP_PROCOPTION LOG_SERVER_START, 'STARTUP', 'OFF'
GO

If you're unsure as to what procedures you've created have been marked to auto-execute, you can run the following query:

SELECT ROUTINE_NAME
FROM MASTER.INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'ExecIsStartup') = 1

One thing you should be aware about: Dropping and re-creating marked stored procedures will require re-running sp_procoption. Dropping a procedure will cause the procedure to be "unmarked" for automatic execution. If you drop the procedure with no intent to re-create it, the system configuration setting 'scan for startup procs' will be left "on" until you manually set it to "off" using sp_configure or by turning off the procedure's auto-execution using sp_procoption. The process of turning procedure auto-execution on and off maintains this system configuration setting automatically.

Next Steps

• Read this SQL Server Books Online article
• Get familiar with the columns in the sys.configurations table
• Read this tip about scripting SQL Server Profiler server side traces

About the author

Armando Prato has close to 30 years of industry experience and has been working with SQL Server since version 6.5.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips