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)
My use case is for a database containing some ‘logging’ tables which grow endlessly, at about 1MB/minute.
This feature lets me automatically start a stored procedure which monitors the size of my database and automatically pulls the oldest records out to archive files when it gets too big.
This happens inside an endless WHILE loop (with an appropriate WAITFOR DELAY statement).