Automatic Execution of Stored Procedures
Stored procedures marked for automatic execution are executed every time SQL Server starts. This is useful if you have operations that you want to perform regularly, or if you have a stored procedure that runs as a background process and is expected to be running at all times. Another use for automatic execution of stored procedures is to have the stored procedure perform system or maintenance tasks in tempdb, such as creating a global temporary table. This ensures that such a temporary table will always exist when tempdb is re-created as SQL Server starts.
A stored procedure that is automatically executed operates with the same permissions as members of the sysadmin fixed server role. Any error messages generated by the stored procedure are written to the SQL Server error log. Do not return any result sets from a stored procedure that is executed automatically. Because the stored procedure is being executed by SQL Server rather than a user, there is nowhere for the result sets to go.
Execution of the stored procedures starts when the master database is recovered at startup.
Setting, Clearing, and Controlling Automatic Execution
Only the system administrator (sa) can mark a stored procedure to execute automatically. In addition, the stored procedure must be in the master database and owned by sa and cannot have input or output parameters.
Use sp_procoption to:
Designate an existing stored procedure as a startup procedure.
Stop a procedure from executing at SQL Server startup.
Although stored procedures are set for automatic execution individually, the SQL Server scan for startup procs configuration option can be set using sp_configure to prevent all stored procedures from executing automatically when SQL Server starts. To skip launching these stored procedures, 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 Trace Flags.
Best Practice
There is no limit to the number of startup procedures you can have, but be aware that each consumes one worker thread while executing. If you must execute multiple procedures at startup but do not need to execute them in parallel, make one procedure the startup procedure and have that procedure call the other procedures. This uses only one worker thread.
To set or clear a stored procedure for automatic execution
To set or clear the scan for startup procs configuration option