Automatically Running Stored Procedures at SQL Server Startup

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.

 

sp_procoption Parameters

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.

source collected from mssqltips.com