Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 attend our next webcast













































   Got a SQL tip?
            We want to know!

Automatically Running Stored Procedures at SQL Server Startup

MSSQLTips author Armando Prato By:   |   Read Comments (2)   |   Related Tips: More > 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.

 

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.

Next Steps

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


Last Update: 9/2/2008


About the author
MSSQLTips author Armando Prato
Armando Prato has over 24 years of industry experience and has been working with SQL Server since version 6.5.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Friday, December 27, 2013 - 1:08:38 PM - Steve Armistead Read The Tip

Very helpful - concise and in-depth. Thanks!


Thursday, September 26, 2013 - 10:29:52 AM - Fabio Dias Read The Tip

Thank you man. This is a old article but very handy, specially to handle the Identity Seeds issue in SQL SERVER 2012.




 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.