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'
Share the Post:
Share on facebook
Share on twitter
Share on linkedin


Recent Articles: