SQL Server – sp_procoption system stored procedure

Is it possible to run stored procedure whenever sql server starts? Answer is yes. SQL Server provides sp_procoption system procedure that will make you to run stored procedure automatically each time sql server service is started.

sp_procoption

Using sp_procoption system stored procedure help you to execute stored procedure when sql server service is started. This option will keep a track of sql server up time.

Syntax: exec sp_procoption ‘stored procedure name’,’startup’,’ON|OFF’

First Input parameter is stored procedure name, second is STARTUP

If you set startup parameter option set to ON that will configure to execute procedure automatically in sql server

Limitation:

  • Must have sysadmin privilege to use sp_procoption.
  • To configure sp_procoption, you should be in master database.
  • Stored procedure that needs to configure must not require any input parameter or output parameter.

 

Example of sp_procoption, we are creating table in testdb to track the sql server uptime using sp_procoption. Now before configuring this option, we have to enable sql server properties “Scan for startup procs ” either using sp_configure or sql server properties wizard..

Using SSMS ->Connect to sql server instance ->right click on sql server name -> Click on properties as shown below.

 

 

 

 

Click on advanced then set scan for procs to true

 

 

Also can be done using below sql command

 

exec sp_configure ‘show advanced options’,1

 

reconfigure

exec sp_configure ‘scan for startup procs’,1

reconfigure

Also it gets configured automatically when run the below sql command

USE MASTER

EXEC SP_PROCOPTION usp_SQLSERVER_UPTIME_LOG, ‘STARTUP’, ‘ON’

Now use the below script to create table and stored procedure which we have to configure to execute automatically when sql server service is started.

Script

USE MASTER

GO

CREATE TABLE SQLSERVER_UPTIME_LOG

(

LOG_ID INT IDENTITY(1,1) NOT NULL,

SQLSERVER_START_TIME DATETIME NOT NULL

CONSTRAINT DF_START_TIME DEFAULT GETDATE()

)

GO

USE MASTER

GO

CREATE PROCEDURE usp_SQLSERVER_UPTIME_LOG

AS

INSERT INTO SQLSERVER_UPTIME_LOG DEFAULT VALUES

After creating stored rocedure, we have to restart sql server service to check whether stored rocedure execute and entries logged in table.

SELECT * FROM SQLSERVER_UPTIME_LOG

Also you can check sql server logs, it will show you below entry.

To disable this property, you have to set automatic execution off by using below sql command; Next time when sql server starts that will not execute any stored procedure

 

USE MASTER

EXEC SP_PROCOPTION usp_SQLSERVER_UPTIME_LOG, ‘STARTUP’, ‘OFF’

 

 

Leave a Reply

  

  

  


5 + = 6