Auto Execution Stored Procedures

It is now possible to have one or more stored procedures execute automatically when SQL Server starts. The stored procedures must be created by the system administrator (SA) and will execute under the SA account as a background process. The procedure(s) cannot have any input parameters.

There is no limit to the number of startup procedures you can have, but be aware that each consumes one connection while executing. If you need to execute multiple procedures at startup, but don't need to execute them in parallel, you can make one procedure the startup procedure and have that procedure call the other procedures. This will use only one connection.

Execution of the stored procedures starts when the last database has been recovered at startup. To skip launching these stored procedures, you can specify trace flag 4022 as a startup parameter. If you start SQL Server with minimal configuration (using the -f flag), the startup stored procedures are not executed. For more information, see the Trace Flags topic or the Utilities and Executables topic.

To create a startup stored procedure, you must be logged in as system administrator and create the stored procedure in the master database.

sp_makestartup procedure_name

sp_unmakestartup procedure_name

sp_helpstartup

For more information, see the specific stored procedures within the System Stored Procedures topic.