>SQL Server: Automatic Query Execution at Every Instance Startup


Though production database servers are design to stay up for 24×7, 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)
Advertisements

Posted on June 3, 2011, in Sql Server 2005, Sql Server 2008, Sql Server Management, Store Procedures, System Databases, TSQL Tips n Tricks. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: