Chapter 1: System Procedures

sp_fixindex

Description

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

Syntax

sp_fixindex dbname, tabname, indid

Parameters

dbname: is the database name

tabname: is the table name

indid: 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:

sp_fixindex pubs2, sysprocedures, 1

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:

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.

Permissions

Only SA can run sp_fixindex.

Auditing

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	Roles – Current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – All input parameters Proxy information – Original login name, if set proxy in effect