HOW TO: Use the sp_fixindex Stored Procedure (106122)

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