Click here to Skip to main content
Click here to Skip to main content

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.

Introduction

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.

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

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:

EXEC SP_PROCOPTION
@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.)

License

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

Share

About the Author

aasim abdullah
Team Leader CureMD
Pakistan Pakistan
Aasim Abdullah is working as SQL Server DBA with CureMD (www.curemd.com) 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.
Follow on   Twitter   Google+

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web04 | 2.8.140814.1 | Last Updated 19 Apr 2013
Article Copyright 2012 by aasim abdullah
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid