sp_fixindex

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).

Syntax

sp_fixindex database_name, table_name [, index_id | null] 
	[, index_name | null]  [, force_option]

Parameters

Examples

Usage

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.
For more information on sp_fixindex, see:
  • Encyclopedia of Tasks in the Troubleshooting and Error Message Guide.

  • Indexing for Performance in the Performance and Tuning Guide: Basics.

Permissions

The permission checks for sp_fixindex differ based on your granular permissions settings.

SettingDescription
Enabled

With granular permissions enabled, you must be the databse owner or a user with own database privilege.

Disabled

With granular permissions disabled, you must be a user with sa_role.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

InformationValues
Event

38

Audit option

exec_procedure

Command or access audited

Execution of a procedure

Information in extrainfo
  • 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