sp_fixindex repairs corrupt indexes on system tables. It can rebuild a specified index or all indexes on the table. sp_fixindex rebuilds the data layer if the target table has a placement or clustered index (it reclaims the unused space in the data layer while working on the placement or clustered index of a system table).
sp_fixindex database_name, table_name [, index_id | null] [, index_name | null] [, force_option]
is the database name
is the table name
is the ID of the index you want to fix
indicates the index that needs to be processed. If a NULL value is used, the index associated with index_id is rebuilt. If index_id is also a NULL value, all the indexes in the system table are rebuilt
forces Adaptive Server to rebuild the system table index in tempdb. sp_fixindex without the force_option forces the database specified by database_name to be in single-user mode, which is not possible for tempdb. Although the force_option allows you to rebuilt system catalogs in tempdb, it should not be used for user databases.
Repairs the clustered index on the sysprocedures table of the pubs2 database:
sp_fixindex pubs2, sysprocedures, 1
Rebuilds the index with an index ID of 2 on testdb..sysprocedures:
sp_fixindex 'testdb', 'sysprocedures', 2
Rebuilds the index csysprocedures in the testdb..sysprocedures system table:
sp_fixindex 'testdb', 'sysprocedures', null, 'csysprocedures'
Rebuilds all available indexes on the sysprocedures table in testdb. If the table has clustered or placement index, sp_fixindex reclaims the unused space by removing the garbage present in data pages (that is, it rebuilds the data pages):
sp_fixindex 'testdb', 'sysprocedures'
Rebuilds the index with an with an index ID of 2 on tempdb..sysprocedures:
sp_fixindex 'tempdb', 'sysprocedures', 2, null, 1
Rebuilds the index csysprocedures for the table tempdb..sysprocedures:
sp_fixindex 'tempdb', 'sysprocedures', null, 'sysprocedures', 1
Rebuilds all indexes on sysprocedures in tempdb:
sp_fixindex 'tempdb', 'sysprocedures', null, null, 1
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.
WARNING! You cannot use sp_fixindex against the clustered index on sysindexes. If you do, sp_fixindex returns the following error message:
Cannot re-create index on this table.
The permission checks for sp_fixindex differ based on your granular permissions settings.
Granular permissions enabled |
With granular permissions enabled, you must be the databse owner or a user with own database privilege. |
Granular permissions disabled |
With granular permissions disabled, you must be a user with sa_role. |
Values in event and extrainfo columns from the sysaudits table are:
Event |
Audit option |
Command or access audited |
Information in extrainfo |
---|---|---|---|
38 |
exec_procedure |
Execution of a procedure |
|
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.