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

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.