INF: How to Benefit From Autoexec Stored Procedures

MORE INFORMATION

In versions 6.0 and 6.5, you can 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.

During start-up, SQL Server checks for stored procedures in the master database that have been marked for start-up, launches the exception handler to handle any exception raised during the execution of the stored procedures and then writes an entry into the SQL Server errorlog. Execution of the stored procedures starts when the last database has been recovered at startup.

The following are some system administration tasks that can benefit from this feature:

1. Correct the page count discrepancy in the SYSLOGS table.
2. Update the statistics on the distribution of the key values in the indexes.
   
   This can be done with the UPDATE STATISTICS statement which creates or updates information about the distribution of the key values in the indexes. This helps SQL Server determine which indexes to use when processing a query. You should use this statement when a large amount of data in an indexed column has been added, changed, or deleted. Please refer to Update Statistics command in the "Transact-SQL Reference" manual for additional information.

Also refer to the "Transact-SQL Reference" manual for information on how to create, delete, gather information, and disable auto execution stored procedures.

SQL Server 6.5 users can use the Database Maintenance Plan Wizard to perform the above mentioned system administration tasks.