A SQL Server administrator can create procedures called autostart stored procedures, which are normal stored procedures that are flagged to be executed automatically when SQL Server starts. Autostart procedures are useful to perform housekeeping type tasks or start up a background process when SQL Server starts. Some possible uses for autostart procedures include the following:
Automatically setting permissions in tempdb.
Creating a global temporary table and keeping the procedure in an indefinite WAITFOR loop to keep the global temp table permanently available.
Enabling "Black Box" recording for SQL Profiler (for more information on "black box" trace files, see Chapter 7.
Automatically starting other external processes on the SQL Server machine using xp_cmdshell. (Using xp_cmdshell is discussed in the "Extended Stored Procedures" section later in this chapter.)
"Priming" the data cache with the contents of your critical, frequently used tables.
"Priming" the procedure cache by executing procedures or functions you want to have compiled and cached before applications start using them.
To create an autostart procedure, log in as a system administrator and create the procedure in the master database. Then set the procedure startup option to true using sp_procoption:
sp_procoption procedure_name, startup, true
If you no longer want the procedure to run at startup, remove the startup option by executing the same procedure and changing the value to FALSE.
You can also set the autostart option within Enterprise Manager for stored procedures that reside in the master database. Right-click on a stored procedure in the master database and choose the Properties option to bring up the Properties dialog box. Put a checkmark in the Execute Whenever SQL Server Starts check box, and click OK or Apply to save the changes to the procedure (see Figure 28.12).
An autostart procedure will run in the context of a system administrator account, but it can use SETUSER to impersonate another account if necessary. If you need to reference objects in other databases from within the startup procedure, you'll need to fully qualify the object with the appropriate database and owner names.
Startup procedures are launched asynchronously; that is, SQL Server doesn't wait for them to complete before continuing with additional startup tasks. This allows a startup procedure to execute in a loop for the duration of the SQL Server process, or allows several startup procedures to be launched simultaneously. While a startup procedure is running, it runs as a separate active user connection.
If you need to execute a series of stored procedures in sequence during startup, nest the stored procedure calls within a single startup procedure. Nested startup procedures consume only a single user connection.
Any error messages or print statements generated by a startup procedure will be written to the SQL Server error log. For example, consider the following whimsical, but utterly useles startup procedure:
use master go create procedure good_morning as print "Good morning, Dave" return go sp_procoption good_morning, startup, true go
When SQL Server is restarted, the following entries would be displayed in the error log:
2001-04-21 01:30:56.03 spid3 Launched startup procedure 'good_morning' 2001-04-21 01:30:56.07 spid51 [autoexec] Good morning, Dave
Any resultsets generated by a startup procedure vanish into the infamous bit bucket. If you need to return resultsets from a startup procedure, write the procedure to insert the results into a table. The table needs to be a permanent table and not a temporary table because a temporary table would be automatically dropped when the startup procedure finishes executing.
The following startup procedure is an example of a procedure that could preload all tables within the Pubs database into data cache memory on SQL Server startup:
use master go create procedure prime_cache as declare @tablename varchar(128) declare c1 cursor for select name from pubs.dbo.sysobjects where type = 'U' open c1 fetch c1 into @tablename while @@fetch_status = 0 begin print 'Loading ''' + @tablename + ''' into data cache' exec ('select * from pubs.dbo.' + @tablename) fetch c1 into @tablename end close c1 deallocate c1 return go sp_procoption prime_cache, startup, true go
The errorlog output from this startup procedure would be as follows:
2001-04-21 01:44:28.93 spid3 Launched startup procedure 'good_morning' 2001-04-21 01:44:28.93 spid3 Launched startup procedure 'prime_cache' 2001-04-21 01:44:28.98 spid52 [autoexec] Good morning, Dave 2001-04-21 01:44:29.16 spid51 [autoexec] Loading 'titleauthor' into data cache 2001-04-21 01:44:29.19 spid51 [autoexec] Loading 'stores' into data cache 2001-04-21 01:44:29.19 spid51 [autoexec] Loading 'sales' into data cache 2001-04-21 01:44:29.19 spid51 [autoexec] Loading 'roysched' into data cache 2001-04-21 01:44:29.20 spid51 [autoexec] Loading 'discounts' into data cache 2001-04-21 01:44:29.20 spid51 [autoexec] Loading 'jobs' into data cache 2001-04-21 01:44:29.23 spid51 [autoexec] Loading 'pub_info' into data cache 2001-04-21 01:44:29.28 spid51 [autoexec] Loading 'employee' into data cache 2001-04-21 01:44:29.30 spid51 [autoexec] Loading 'test' into data cache 2001-04-21 01:44:29.30 spid51 [autoexec] Loading 'authors' into data cache 2001-04-21 01:44:29.32 spid51 [autoexec] Loading 'publishers' into data cache 2001-04-21 01:44:29.33 spid51 [autoexec] Loading 'titles' into data cache
If a startup procedure is creating problems on startup, or if you simply want to prevent startup procedures from executing, start the server using trace flag 4022. You can set the trace flag in Enterprise Manager by using the SQL Server Properties dialog box. Just right-click on the name of your server and choose Properties to bring up the SQL Server Properties dialog box. Click on the Startup Parameters button, enter the -T4022 trace flag in the Parameter text box, and click the Add button (see Figure 28.13).