Knowledge Base |

# HOW TO: Use the sp_fixindex Stored Procedure

Article ID: 106122

Article Last Modified on 6/24/2004

APPLIES TO

- Microsoft SQL Server 2000 Standard Edition
- Microsoft SQL Server 2000 64-bit Edition
- Microsoft SQL Server 7.0 Standard Edition
- Microsoft SQL Server 7.0 Service Pack 1
- Microsoft SQL Server 6.5 Standard Edition
- Microsoft SQL Server 6.5 Service Pack 1
- Microsoft SQL Server 6.5 Service Pack 1
- Microsoft SQL Server 6.5 Service Pack 2
- Microsoft SQL Server 6.5 Service Pack 3
- Microsoft SQL Server 6.5 Service Pack 4
- Microsoft SQL Server 6.5 Service Pack 5a
- Microsoft SQL Server 6.5 Service Pack 5a
- Microsoft SQL Server 6.0 Standard Edition
- Microsoft SQL Server 4.21a Standard Edition

This article was previously published under Q106122

### IN THIS TASK

- SUMMARY

- Function of the sp_fixindex Stored Procedure
- Syntax for the sp_fixindex Stored Procedure
- How to Use the sp_fixindex Stored Procedure
- Example

- REFERENCES

## SUMMARY

This article discusses how to use the undocumented sp_fixindex system stored procedure to correct problems in the indexes of SQL Server system tables.
Stored procedure name: sp_fixindex
SQL SERVER versions: The sp_fixindex stored procedure does not exist in versions of SQL Server earlier than 4.2.
back to the top

### Function of the sp_fixindex Stored Procedure

You cannot drop the indexes of the system tables for SQL Server by using the DROP INDEX command. You can use the undocumented sp_fixindex stored procedure to drop, re-create, and index a system table, allowing sites that experience allocation or data errors in these indexes to address the problem.
The sp_fixindex stored procedure uses the undocumented DBCC REPAIRINDEX command.
NOTE: Always make a backup of your database before you run the sp_fixindex stored procedure.
back to the top

### Syntax for the sp_fixindex Stored Procedure

Here is the syntax for the command:

       sp_fixindex dbname, tablename, indid

Where:
dbname is the name of the database with the problem index.
tablename is the name of the table with the problem index.
indid is the index id of the problem index.
back to the top

### How to Use the sp_fixindex Stored Procedure

If you do not already know the indid of the affected index, find it by viewing the output of this SELECT statement:

       select name, indid
       from sysindexes
       where id = object_id('tablename')

The database must be in single user mode, and you must run the sp_fixindex stored procedure in the database. If multiple indexes have problems, you must run sp_fixindex individually for each problem index.
Always verify that the problems have been fully corrected without creating data integrity problems by running a DBCC CHECKDB and CHECKALLOC statement after the sp_fixindex stored procedure completes. If the CHECKDB and CHECKALLOC statements will take too long, you can run a quick check by using the DBCC CHECKTABLE statement; however, you must still run the CHECKDB and CHECKALLOC statements, when you have time. You can use the sp_fixindex stored procedure to correct errors like 605's in the index pages of a system table. If the errors are in the data pages of the system table, the user can only restore from their last backups.
If sp_fixindex has been run on the master's system catalog, restart the service before you run the DBCCs.
back to the top

### Example

In this example, you use the sp_fixindex to correct the clustered index in the sysprocedures system table for a database that is named cheers.

       use master
       go
       sp_dboption cheers,single,true
       go
       use cheers
       go
       checkpoint
       go
       sp_fixindex cheers,sysprocedures,1
       go
       use master
       go
       sp_dboption cheers,single,false
       go
       use cheers
       go
       checkpoint
       go
       dbcc checkalloc
       go
       dbcc checkdb
       go

back to the top

Additional query words: Windows NT

Keywords: kbhowtomaster kbusage KB106122