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.
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.
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.
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.
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 |
The information in this article applies to:
- Microsoft SQL Server 2000 (all editions)
- Microsoft SQL Server 4.2x (Version: 4.2x)
- Microsoft SQL Server 6.0 (Version: 6.0)
- Microsoft SQL Server 6.5 (Version: 6.5)
- Microsoft SQL Server 6.5 Service Pack 1 (Version: 6.5 Service Pack 1)
- Microsoft SQL Server 6.5 Service Pack 1 and later (Version: 6.5 Service Pack
1 and later)
- Microsoft SQL Server 6.5 Service Pack 2 and later (Version: 6.5 Service Pack
2 and later)
- Microsoft SQL Server 6.5 Service Pack 3 and later (Version: 6.5 Service Pack
3 and later)
- Microsoft SQL Server 6.5 Service Pack 4 and later (Version: 6.5 Service Pack
4 and later)
- Microsoft SQL Server 6.5 Service Pack 5 and 5a (Version: 6.5 Service Pack
5 and 5a)
- Microsoft SQL Server 6.5 Service Pack 5a (Version: 6.5 Service Pack 5a)
- Microsoft SQL Server 7.0 (Version: 7.0)
- Microsoft SQL Server 7.0 Service Pack 1 (Version: 7.0 Service Pack 1)
© 2003 Microsoft