sp_fixindex

Description

Repairs the index on one of your system tables when it has been corrupted.

Syntax

sp_fixindex dbname, table_name, index_id

Parameters

dbname

is the database name

table_name

is the table name

index_id

is the ID of the index you want to fix

Examples

Example 1

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

Usage

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:

Do not run sp_fixindex on user tables.

Repairing a nonclustered index on sysobjects using sp_fixindex requires additional steps.

Permissions

Only SA can run sp_fixindex.

See also

For more information on sp_fixindex, see Chapter 2, “Encyclopedia of Tasks” in the Troubleshooting and Error Message Guide.