SQL Server sp_procoption startup procedures

The sp_procoption is a SQL Server system stored procedure allows us to Set or clear a stored procedure for automatic execution when an instance of SQL Server is started.

A startup procedures must be in the master database, and cannot contain input or output parameters.

When a startup Stored procedure is set to automatic execution, it executes each time when an instance of SQL Server is started.

Suppose you want to execute any SQL scripts on startup of SQL Server services, then you an create your own customized stored procedure in master database that will be executed on startup of SQL Server Services also can set it for automatic execution.

SYNTAX

sp_procoption procedure_name, 

option_name, 

option_value

procedure_name specify the name for stored procedure for which to set an option.  

option_name is the name of the option to set. The only value for option is startup.

option_value it could be either ON (true or on) or OFF (false or off).

Implementing of SQL Server Startup Procedures

First we will Create a table named SqlServer_Startup_Log in master database that will store a log SQL Server startup log details.

USE Master
GO

CREATE TABLE SqlServer_Startup_Log
(
LogId INT IDENTITY(1,1),
ServiceName VARCHAR(100),
ServerStartup_Time DATETIME
)
GO

Now we will create a stored procedure named proc_InsertStratupLog to insert the startup log details into table SqlServer_Startup_Log. You can also write other scripts to inside the sp that you want to execute on SQL Server Startup.

In ServiceName column, we are inserting SQL Server service name using @@servicename function.

CREATE PROC proc_InsertStratupLog
AS
BEGIN

INSERT INTO master..SqlServer_Startup_Log
(ServiceName, ServerStartup_Time)
VALUES
(@@servicename, Getdate())

END

Now we will configure the Stored Procedure proc_InsertStratupLog for auto execution on SQL Server services startup.

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

Now you have successfully configured the startup procedures. You can quickly check whether the Startup procedure is created or not using below SQL Statemment.

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

As you can see, a startup procedure named as proc_InsertStratupLog is there in result set.

Now time to verifying the implementation of startup procedure, lets check whether the procedures is executed and save the startup logs to table or not when SQL Server services is started.




First, we verify the records in Log table SqlServer_Startup_Log to ensure that there is no record in table.

SELECT * FROM master.dbo.SqlServer_Startup_Log

As you can see, log table returns blank data. Now we will restart the SQL Server Services to see whether the Startup procedure run on startup or not. If it runs without any error then it will insert the startup log into table.

To restart the SQL Server Services Go to Object Explorer then right Click on SQL Server instance then click on Restart in context menu as shown below.

Once you click on Restart,  You will get a confirmation dialog box, click on Yes button.

Clicking on Yes button, restart the SQL Server Services.

Once it is completed, Lets check the log table SqlServer_Startup_Log.

As you can see, a startup log is inserted into log table.

SELECT * FROM master.dbo.SqlServer_Startup_Log

Stop auto execution of Startup procedures

Following SQL statement stop the auto execution of Startup procedure proc_InsertStratupLog. For this you need to set option value to OFF.

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

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

Also Read..

SQL Server sp_rename

SQL Server sp_help

SQL Server sp_monitor

SQL Server sp_executesql