INF: How to Benefit From Autoexec Stored Procedures

• Microsoft SQL Server, versions 6.0 & 6.5

SUMMARY

This article explains how to benefit from auto executing stored procedures to perform certain system administration tasks.

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.

   See the following Knowledge Base article for more information:

         Q39113, PRB: DBCC Reports Page Count Discrepancy on 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.

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