SQL Server: 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.)