SQL Server: Automatic Query Execution at Every Instance Startup

Though production database servers are design to stay up for 24x7, but still when ever these production database servers go down and restart, sometime 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 store procedure to encapsulate all these queries. Then automatic execution of this stored procedure is achieved by using the sp_procoption system stored procedure.

(Note: Best place to store such stored procedure is 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. Syntax will be as follow:

EXEC SP_PROCOPTION

@ProcName = 'Proc_InsertStartupTime',

@OptionName = 'STARTUP',

@OptionValue = 'TRUE'

After executing above statement, when ever SQL Server instance will restart, 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 stored procedure from automatic execution, we will use following syntax.

EXEC sp_procoption

@ProcName = 'Proc_InsertStartupTime',

@OptionName = 'STARTUP',

@OptionValue = 'OFF'

(Applicable for SQL Server 2005 and above versions)