SQL 2000 Only
This script will create a stored procedure and a table in the master database. Simply run the stored procedure and supply a database name as a parameter. It will do the following:
1. Gather/Log DBCC SHOWCONTIG statistics (pre defrag)
2. Defragment all user indexes in the database
3. Gather/Log DBCC SHOWCONTIG statistics (post defrag)
4. DBCC DBREINDEX for all indexes that have %10 logical fragmentation
5. Gather/Log DBCC SHOWCONTIG statistics for reindexed tables.
All activity is logged in [master].[dbo].[fragreport]
You can create a job to schedule this and use the fragreport to identify problem areas with your indexes/database use.
Have fun!
Daniel Mross
dmross@gmail.com
New Version - works with table not owned by dbo
