Repairs the index on one of your system tables when it has been corrupted.
sp_fixindex dbname, table_name, index_id
is the database name
is the table name
is the ID of the index you want to fix
In this example, sp_fixindex repairs the clustered index on the sysprocedures table of the pubs2 database:
1> sp_fixindex pubs2, sysprocedures, 1 2> go
WARNING! Do not run sp_fixindex on the clustered index of the sysobjects or sysindexes tables or on user tables. If you do, sp_fixindex returns the following error message:
The index with id 1 on sysobjects cannot be recreated.
Before you run sp_fixindex, make sure your database is in single-user mode, and is reconfigured to allow updates to system tables.
After you run sp_fixindex:
Use the dbcc checktable command to verify that the corrupted index has been fixed
Disallow updates to system tables using sp_configure
Turn off single-user mode
Do not run sp_fixindex on user tables.
Repairing a nonclustered index on sysobjects using sp_fixindex requires additional steps.
Only SA can run sp_fixindex.
Documents For more information on sp_fixindex, see:
Chapter 2, “Encyclopedia of Tasks” in the Troubleshooting and Error Message Guide.
Chapter 13, See “Indexing for Performance” in the Performance and Tuning Guide: Basics.
