Autostart Stored Procedures

SQL Server has the ability to mark a stored procedure as autostart. Autostart stored procedures are useful if you want to perform housekeeping functions regularly or if you have a background daemon procedure that's always expected to be running. Another handy use for an autostart procedure is to have it assign some privileges in tempdb. Or the procedure can create a global temporary table and then sleep indefinitely using WAITFOR. This will ensure that such a temporary table will always exist, because the calling process is the first thing executed and it never terminates.

You can use the system stored procedure sp_procoption to make a stored procedure start automatically. This procedure allows you to turn options for stored procedures on or off. In SQL Server 2000, the only available option is startup. Here's the syntax of the command to enable a procedure to automatically execute on SQL Server startup:

 sp_procoption procedure_name, startup, true 

You can remove the startup option by executing the same procedure and changing the value to FALSE. A procedure that's autostarted runs in the context of a system administrator account. (The procedure can use SETUSER to impersonate another account.) A procedure with the startup option set to TRUE must be in the master database and must be owned by the database owner (dbo) in master. You can, of course, reference objects in other databases from within the startup procedure or even call procedures that exist in other databases. A startup procedure is launched asynchronously, and it can execute in a loop for the entire duration of the SQL Server process. This allows several such procedures to be launched simultaneously at startup. When a startup procedure is running, it's seen as an active user connection.

A single startup procedure can nest calls to other stored procedures, consuming only a single user connection. Such execution of the nested procedures is synchronous, as would normally be the case. (That is, execution in the calling procedure doesn't continue until the procedure being called completes.) Typically, a stored procedure that's autostarted won't generate a lot of output. Errors, including those raised with RAISERROR, will be written to the SQL Server error log, and any result sets generated will seemingly vanish. If you need the stored procedure to return result sets, you should use a stored procedure that calls the main stored procedure with INSERT/EXEC to insert the results into a table.

If you want to prevent a procedure with the startup option from executing, you can start the server using trace flag 4022 or as a minimally configured server using the -f switch to sqlservr.exe. You can set the -T4022 flag by using the SQL Server Properties dialog box in SQL Server Enterprise Manager. (Right-click on the name of your server and choose Properties to get to this dialog box.) These safeguards allow you to recover from problems. Consider the illustrative but perhaps absurd example of someone including a procedure that executes the SHUTDOWN command. If you had given such a procedure the autostart option, SQL Server would immediately shut itself down before you could do anything about it!