Welcome to today’s blog post where we will be discussing a lesser-known feature of SQL Server called startup procedures. Although this feature has been around for quite some time, it remains relatively unknown to many SQL Server users. In this article, we will provide a quick introduction to startup procedures and explore a practical example of how they can be used.

What are Startup Procedures?

In SQL Server, startup procedures allow you to specify a set of code that will run automatically when the SQL Server service starts. This can be incredibly useful for performing tasks such as logging server start times, cleaning up data, or creating temporary tables.

Creating a Startup Procedure

Let’s walk through an example of creating a startup procedure that logs the time of the SQL Server service restart.

Step 1: Create a Table and Stored Procedure

First, we need to create a table to hold the log time. We can do this by executing the following SQL statement:

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

Next, we will create a stored procedure that inserts the current time into the table:

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

Step 2: Mark Startup Procedures

Now that we have our table and stored procedure, we need to mark the procedure to run at startup. We can do this by executing the following script:

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

Step 3: Test Startup Procedures

Let’s test our startup procedure to see if it runs when the SQL Server service starts. First, execute a select statement that returns no results. Then, restart your SQL Server services. After the restart, execute the same select statement again. You should see a new entry in the result set, indicating the recent SQL Server restart time.

Step 4: DMV to List Startup Procedures

If you want to view a list of all the procedures that are marked to run at startup, you can use the following script:

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

Step 5: Configuration

When a stored procedure is marked for startup, it automatically changes the server configuration and turns on the ‘default trace enabled’ configuration value. However, if for any reason this configuration is not enabled, you can manually turn it on by executing the following script:

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

Step 6: Clean Up

If you no longer need a stored procedure to run at startup, you can unmark it by executing the following script:

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

Finally, to clean up the example we created, you can drop the table and stored procedure using the following script:

DROP TABLE ServerStartHistory
DROP PROCEDURE StartUpProc

We hope this article has provided you with a better understanding of SQL Server startup procedures and how they can be utilized. We would love to hear from you – do you currently use this feature in your production server? If so, please leave a comment below and share your use case scenario with us.