Startup stored procedures

Stored procedures are enabled for execution with the proc sp_procoption. Create your stored procedure in the master database and then call sp_procoption like so…

sp_procoption @ProcName = 'my_stored_procedure'
, @OptionName = 'startup'
, @OptionValue = 'on'

By calling sp_procoption, you are at the same time enabling another configuration setting, “scan for startup procs” if it is not already enabled. After you restart your SQL server service both the stored proc and the configuration setting take effect.

To disable your proc from execution at startup, run the same proc sp_procoption but this time set the @OptionValue to “off”. By doing this, next time your SQL server service is restarted, your proc will not execute. Also by doing this, the configuration option “scan for startup procs” is NOT disabled.

To see what procs you have set for startup, query select * from master.sys.procedures.

An excellent use for a startup proc is a proc that runs trace flags. If at anytime while SQL is running you can disable your trace flags by running DBCC TRACEOFF.

This entry was posted in Tips. Bookmark the permalink.