Automatically Execute Stored Procedures When Starting SQL Server

Automatically Execute Stored Procedures When Starting SQL Server

Use sp_procoption to execute a stored procedure automatically at the time of starting SQL server. The stored procedure that needs to be executed should be created in the master database and owned by a member of the sysadmin role. You can use this procedure to automate tasks like backups and index recreation and any other database maintenance tasks. To make a STORED PROCEDURE auto executable at startup, first login to the master database as SA. Then create a stored procedure (say ‘xxx’) and run this SQL:

                              sp_procoption 'xxx','startup',true

To check whether a procedure is automatically set to execute, run this SQL:

                              sp_procoption 'xxx','startup'

To get the status of all stored procedure in the master database for automatic execution, run this SQL:

                             sp_procoption null,'startup'
See also  5 Tips to Manage a Remote Workforce

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist