sp_fixindex

Description

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

dbname: is the database name

tabname: is the table name

indiex_id: is the ID of the index you want to fix

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

force_option: 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.

Examples

Example 1

Repairs the clustered index on the sysprocedures table of the pubs2 database:

sp_fixindex pubs2, sysprocedures, 1

Example 2

Rebuilds the index with an index ID of 2 on testdb..sysprocedures:

sp_fixindex 'testdb', 'sysprocedures', 2

Example 3

Rebuilds the index csysprocedures in the testdb..sysprocedures system table:

sp_fixindex 'testdb', 'sysprocedures', null, 'csysprocedures'

Example 4

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'

Example 5

Rebuilds the index with an with an index ID of 2 on tempdb..sysprocedures:

sp_fixindex 'tempdb', 'sysprocedures', 2, null, 1

Example 6

Rebuilds the index csysprocedures for the table tempdb..sysprocedures:

sp_fixindex 'tempdb', 'sysprocedures', null,
    'sysprocedures', 1

Example 7

Rebuilds all indexes on sysprocedures in tempdb:

sp_fixindex 'tempdb', 'sysprocedures', null, null, 1

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.

Permissions

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.

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