Registrations are now open. Join us today!
There is still a lot of work to do on the wiki yet! More information about editing can be found here.
Already have an account?

Microsoft KB Archive/106122

From BetaArchive Wiki

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

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