Microsoft KB Archive/106122

= 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

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.