INF: Use of sp_fixindex

ID: Q106122

The information in this article applies to:

Microsoft SQL Server versions 4.2x, 6.0, 6.5, 6.5 Service Pack 1, 6.5 Service Pack 1 and later, 6.5 Service Pack 2 and later, 6.5 Service Pack 3 and later, 6.5 Service Pack 4 and later, 6.5 Service Pack 5 and 5a, 6.5 Service Pack 5a, 7.0, 7.0 Service Pack 1

SUMMARY

This article discusses how to use the undocumented system stored procedure sp_fixindex to correct problems in the indexes of SQL Server system tables.

MORE INFORMATION

NAME: sp_fixindex

SQL SERVER VERSIONS: sp_fixindex does not exist in versions of SQL Server prior to 4.2.

Function

It is not possible to drop the indexes of the system tables for SQL Server using the normal DROP INDEX command. The undocumented command sp_fixindex can be used to drop and recreate and index on a system table, allowing sites which encounter allocation or data errors in these indexes to address the problem.

sp_fixindex uses the undocumented dbcc repairindex command.

The site should always make a backup of their database before running sp_fixindex.

Syntax


   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.

How To Use

If you do not already know the indid of the affected index, find it by viewing the output of the following select:


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

The database must be in single user mode, and sp_fixindex must be executed within the database. If multiple indexes have problems, sp_fixindex must be run individually for each problem index.

Always verify that the problems have been fully corrected without creating data integrity problems by running dbcc checkdb and checkalloc after sp_fixindex completes. If checkdb and checkalloc will take too long, a quick check can be made by running a dbcc checktable, but checkdb and checkalloc should still be run when time allows.

sp_fixindex can be used 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 master's system catalog, restart the service before running dbccs.

Example

In this example sp_fixindex is used to correct the clustered index in the sysprocedures table for a database 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

Additional query words: Windows NT

Keywords : kbusage kbSQLServ600 kbSQLServ650 kbSQLServ700 kbSQLServ650sp5 kbSQLServ700sp1 SSrvWinNT
Version : winnt:4.2x,6.0,6.5,6.5 Service Pack 1,6.5 Service Pack 1 and later,6.5 Service Pack 2 and later,6.5 Service Pack 3 and later,6.5 Service Pack 4 and later,6.5 Service Pack 5 and 5a,6.5 Service Pack 5a,7.0,7.0 Service Pack 1
Platform : winnt
Issue type : kbinfo