The stored procedure is automatically executed when SQL Server is started. Page 1/2

SQL Server provides the system stored procedure sp_procoption, which can be used to assign one or more stored procedures to be automatically executed when the SQL Server service starts. This is a good choice and can be used for a variety of purposes. For example, you may have a high overhead query in your database. This query takes some time for the first execution. By using sp_procoption, you can run this query at server startup to pre-compile the execution plan. Thus, a user of yours will not be the first unfortunate person to run this special query. I used this feature to set up an automatic execution feature for server-side tracking of the summary analyzer I wrote myself. This tracking feature became part of the storage process set to be automatically executed when the server was started.
Copy codeThe Code is as follows:
Sp_procoption Parameters
Exec sp_procoption @ ProcName = ['stored procedure name'],
@ OptionName = 'startup ',
@ OptionValue = [on | off]

The following is an explanation of the sp_procoption stored procedure parameters:

L parameter @ ProcName indicates the name of the automatic execution process.

L parameter @ OptionName is optional. the only valid option is STARTUP.

L parameter @ OptionValue switch the ON and OFF of Automatic execution.

Use sp_procoption with specific restrictions:

L you must log on to the server as a system administrator and use sp_procoption.

L you can only assign standard stored procedures, extended stored procedures, or CLR Stored Procedures to start.

L The stored procedure must be in the master-slave database.

L stored procedures do not require any input parameters or return any output parameters.

In the following example, I create a stored procedure, which is automatically executed every time I start my SQL Server instance. The purpose of this stored procedure is to write a row in the database table that records the service start time. Through this database table, I came up with the idea of getting the normal running time of the server. The following script creates a new database that stores a metric table named SERVER_STARTUP_LOG. This table retains the date and time when the last server was started. Once this basic architecture is built, I create a stored procedure that is used to insert the date and time to the table at server startup. Note that the stored procedure is created on the master-slave database.