In its collection of undocumented stored procedures, SQL Server 2000 has a function called sp_fixindex that can be used to repair the index for a given system table. System table indexes are crucial elements of a smooth-running SQL Server, making it necessary for you to be able to easily fix corrupted indexes.
You know you have a corrupt system table index when you get log error 605. Fixing the index with sp_fixindex can save you the hassle of having to restore the database from a backup.
Stored procedure syntax
sp_fixindex <dbname>,<tablename>,<index_id>
dbname is the name of the database to repair, tablename is the name of the system table with the damaged index and index_id is the index ID number for the problem index. If you don't know the index ID number, you can derive it with a quick query:
SELECT NAME, INDID FROM SYSINDEXES WHERE
ID=OBJECT_ID('<tablename>')
Putting the database in single-user mode
You must first put the database in single-user mode before you can run sp_fixindex. One way to do this is with the sp_dboption procedure:
EXEC SP_DPOPTION <database>, SINGLE, TRUE
Taking the database out of single-user mode
To take the database out of single-user mode, use the following procedure:
EXEC SP_DPOPTION <database>, SINGLE, FALSE
You must also run sp_fixindex separately for each index you want to repair. It is possible to run sp_fixindex against the system catalogs, but you must stop and restart the SQL Server service after you do this.
Always run DBCC CHECKDB and CHECKALLOC statements against the repaired database after running sp_fixindex.
About the author: Serdar Yegulalp is editor of the Windows Power Users Newsletter. Check it out for the latest advice and musings on the world of Windows network administrators -- and please share your thoughts as well!
More information from SearchSQLServer.com
Tip: Undocumented stored procedure: View SQL Server 2000 raw data
Tips: Check out our complete collection of stored procedures
Downloads: To access free Windows IT software downloads related to SQL Server visit our Download Library
