Problem solve Get help with specific problems with your technologies, process and projects.

Undocumented stored procedure: Repair table indexes with sp_fixindex

One undocumented stored procedure in SQL Server 2000 can be used to repair a given system table index. Serdar Yegulalp explains how to use sp_fixindex to fix corrupted indexes and ensure a smooth-running SQL Server.

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


  • This was last published in August 2005

    Dig Deeper on SQL-Transact SQL (T-SQL)

    Start the conversation

    Send me notifications when other members comment.

    By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

    Please create a username to comment.

    -ADS BY GOOGLE

    SearchBusinessAnalytics

    SearchDataCenter

    SearchDataManagement

    SearchAWS

    SearchOracle

    SearchContentManagement

    SearchWindowsServer

    Close