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

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 the SAP ASE 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.

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.

Setting	Description
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:

Information	Values
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