Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: Enterprise
Expertise: Intermediate
Jun 24, 1999



Building the Right Environment to Support AI, Machine Learning and Deep Learning

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'
KulBhushan Sharma
Thanks for your registration, follow us on our social networks to keep up-to-date