Click here to Skip to main content
13,298,348 members (80,253 online)
Click here to Skip to main content
Add your own
alternative version


1 bookmarked
Posted 19 Oct 2012

SQL Server: Automatic Query Execution at Every Instance Startup

, 19 Apr 2013
Rate this:
Please Sign up or sign in to vote.
Automatic query execution at every instance startup.


Though production database servers are designed to stay up for 24x7, still whenever these production database servers go down and restart, sometimes we need to execute some queries automatically on every start-up, like clean some setup tables or capture some sort of necessary data which is only available at instance start-up. 

For such queries which need to be executed automatically at every start-up, we have to create a stored procedure to encapsulate all the queries. Then automatic execution of this stored procedure is achieved by using the sp_procoption system stored procedure.

Using the code 

(Note: The best place to store such a stored procedure is the MASTER database). Let’s create a stored procedure to store instance start-up time in a log table.

--Create table to hold startup time
CREATE TABLE dbo.InstanceLog
(StartupTime DATETIME) 
--Create stored procedure to execute on startup automatically
CREATE PROCEDURE dbo.Proc_InsertStartupTime
INSERT dbo.InstanceLog

Now we will use SP_PROCOPTION to tell SQL Server that we want to execute our stored procedure at every instance start-up. The syntax is as follows:

@ProcName = 'Proc_InsertStartupTime',
@OptionName = 'STARTUP',
@OptionValue = 'TRUE'

After executing the above statement, whenever SQL Server instance restarts, the stored procedure will be executed automatically and a new row in our log table dbo.InstanceLog will be inserted.

To revert this option and to stop the stored procedure from automatic execution, we will use the following syntax:

EXEC sp_procoption
@ProcName = 'Proc_InsertStartupTime',
@OptionName = 'STARTUP',
@OptionValue = 'OFF'

(Note: Applicable for SQL Server 2005 and above versions.)


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

aasim abdullah
Team Leader CureMD
Pakistan Pakistan
Aasim Abdullah is working as SQL Server DBA with CureMD ( based in NY, USA. He has been working with SQL Server since 2007 (Version 2005) and has used it in many projects as a developer, administrator, database designer. Aasim's primary interest is SQL Server performance tuning. If he finds the time, he like to sketch faces with graphite pencils.

You may also be interested in...


Comments and Discussions

-- There are no messages in this forum --
Permalink | Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.171207.1 | Last Updated 19 Apr 2013
Article Copyright 2012 by aasim abdullah
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid