Microsoft KB Archive/151366

= INF: How to Benefit From Autoexec Stored Procedures =

Article ID: 151366

Article Last Modified on 8/15/2006

-

APPLIES TO


 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q151366



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.
 * 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.

Additional query words: sp_unmakestartup sp_makestartup sp_helpstartup

Keywords: kbhowto kbusage KB151366

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.