SQL SERVER – Quick Introduction to Startup Procedures

In today’s blog we are going to talk about the SQL Server feature which actually exists for a quite a while but interestingly enough it is not known much. Let us have a quick introduction to startup procedures.

In SQL Server we can mark any procedure to run at Startup. These procedures can be very helpful if you want to run a set of code when you start SQL Server service. In the past, I have used startup parameters for quite a few things like

• Log the start time of the server
• Clean up data from few tables
• Create a global temporary table to hold a temporary table

Well, the above list is just a few examples, however, there can be different uses for this feature.

In this blog post, we will see one example of the startup procedure which will log in the time of the SQL Server service restart.

Step 1: Create a Table and Stored Procedure

Let us first create a table which will hold LogTime.

CREATE TABLE ServerStartHistory
(ID INT IDENTITY(1,1), LogTime DATETIME DEFAULT GETDATE())
GO

Now let us create a stored procedure which will insert the values into this table.

CREATE PROCEDURE StartUpProc
AS
INSERT INTO ServerStartHistory (LogTime)
DEFAULT VALUES
GO

Step 2: Mark Startup Procedures

Here is the script which will mark the procedure to run as a startup.

USE MASTER
GO
EXEC SP_PROCOPTION StartUpProc, 'STARTUP', 'ON'
GO

Step 3: Test Startup Procedures

As we are going to if the procedure runs at the startup or not. First, let us run a select statement which will return us no results.

Next, restart your SQL Server services.

After a restart, you may run the same select again. You will see that in the new result set, there will be a new entry with a record of the recent SQL Server restart time.

Step 4: DMV to List Startup Procedures

Here is the startup procedure which will list all the procedures which are marked to run at Startup.

SELECT [name]
FROM sysobjects
WHERE type = 'P'
AND OBJECTPROPERTY(id, 'ExecIsStartUp') = 1

Step 5: Configuration

Whenever we mark a stored procedure for startup, at that time it will automatically change the server configuration and turns on the configuration value ‘default trace enabled‘ to ON. However, due to any reasons, the configuration is not on, you can manually turn that on by running the following script.

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'default trace enabled';
GO

Step 6: Clean up

Here is the script which will unmark the stored procedure which was previously marked for startup.

USE MASTER
GO
EXEC SP_PROCOPTION StartUpProc, 'STARTUP', 'OFF'
GO

Here is the cleanup script to drop the stored procedure and table which we created for this example.

DROP TABLE ServerStartHistory
GO
DROP PROCEDURE StartUpProc
GO

Let me ask you one question – do you use this feature in your production server? If yes, would you please comment, so we can learn about various use case scenario for this feature.

Reference: Pinal Dave (http://blog.SQLAuthority.com)