Automatically Running Stored Procedures at SQL Server Startup
September 3rd 2008 05:52
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_STA RTUP_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
results
Value Value_in_use description
0 0 scan for startup sp's
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
results
Value Value_in_use description
1 0 scan for startup sp's
If we re-start the SQL Server service, we see that the configuration value now takes effect
results
Value Value_in_use description
1 1 scan for startup sp's
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
results
LOGID START_TIME
1 2008-08-25 21:36:07.547
Lastly, examining the SQL Server error log also verifies the procedure has been automatically run.
USE MASTER
GO
EXEC XP_READERRORLOG
GO
2008-08-25 21:36:07.510 spid5s Launched startup procedure 'LOG_SERVER_START'
2008-08-25 21:36:07.540 spid51s 'LOGGING SERVER STARTUP TIME'
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.ROU TINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUT INE_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.
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_STA RTUP_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
results
Value Value_in_use description
0 0 scan for startup sp's
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
results
Value Value_in_use description
1 0 scan for startup sp's
If we re-start the SQL Server service, we see that the configuration value now takes effect
results
Value Value_in_use description
1 1 scan for startup sp's
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
results
LOGID START_TIME
1 2008-08-25 21:36:07.547
Lastly, examining the SQL Server error log also verifies the procedure has been automatically run.
USE MASTER
GO
EXEC XP_READERRORLOG
GO
2008-08-25 21:36:07.510 spid5s Launched startup procedure 'LOG_SERVER_START'
2008-08-25 21:36:07.540 spid51s 'LOGGING SERVER STARTUP TIME'
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.ROU TINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUT INE_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.
| 47 |
| Vote |
Shared on
Subscribe to this blog
Comment by TimmyH
Tech News
Can you HACK it?
Genyration