Here are three general purpose scripts for degragmenting and speeding up large databases. You can schedule the SPs for periodic execution, but they should be run in absence of user activity on the database (might slow down users otherwise). The SPs were tested against SQL Server 7.0 but should also work on SQL Server 6.5 and 2000.
The scripts are as follows:
exec sp_CheckIdent — checks identity colulmns for all user tables
exec sp_AdminReindexTables — reindexes all user tables
exec sp_AdminRecompileStoredProc — recompiles all SPs
The script has been updated as of 1/7/02 with corrections and user input added to the comments text. The corrections address scenarios when table names might have spaces or weird symbols in them.
Author: Alexei Akimov
Disclaimer: We hope that the information on these script pages is
valuable to you. Your use of the information contained in these pages,
however, is at your sole risk. All information on these pages is provided
“as -is”, without any warranty, whether express or implied, of its accuracy,
completeness, or fitness for a particular purpose…
Back to Database Journal Home