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.

Additional query words: sp_unmakestartup sp_makestartup sp_helpstartup

Keywords          : kbusage SSrvStProc SSrvTran_SQL 
Version           : 6.0 6.5
Platform          : WINDOWS 
Issue type        : kbhowto 

Last Reviewed: March 25, 1999