Undocumented stored procedure: Repair table indexes with sp_fixindex

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_db

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Database Development Managing the development lifecycle with Visual Studio Team System 2008 Processing XML files with SQL Server functions A first look at Visual Studio Team System 2008 Database Edition How to create a SQL inner join and outer join: Basics to get started New datetime data types in SQL Server 2008 offer flexibility Using DATEADD and DATEDIFF to calculate SQL Server datetime values SQL Server database design disasters: How it all starts SQL Server database design disasters: What not to do Secure SQL Server from SQL injection attacks Check SQL Server database and log file size with this stored procedure SQL/Transact SQL (T-SQL) Working with sparse columns in SQL Server 2008 Determining the source of full transaction logs in SQL Server New GROUP BY option provides better data control in SQL Server 2008 Using the OPENROWSET function in SQL Server Loading data files with SQL Server's BULK INSERT statement Importing and exporting bulk data with SQL Server's bcp utility Testing transaction log autogrowth behavior in SQL Server Securing SQL Server with access control, login monitoring and DDL triggers Top 10 SQL Server development tips of 2008 The sqlcmd utility in SQL Server SQL/Transact SQL (T-SQL) Research SQL Server Stored Procedures Top 10 SQL Server development tips of 2008 SQL Server trigger vs. stored procedure to receive data notification SQL Server errors, failures and other problems fixed from the trenches SQL Server and data manipulation in T-SQL How to use SQL Server 2008 hierarchyid data type SQL Server stored procedures tutorial: Write, tune and get examples Check SQL Server database and log file size with this stored procedure SQL Server source code analysis and management adds database security Configure SQL Server Service Broker for sending stored procedure data Find size of SQL Server tables and other objects with stored procedure

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary ACID  (SearchSQLServer.com) commit  (SearchSQLServer.com) DAO  (SearchSQLServer.com) fetch  (SearchSQLServer.com) OLE DB  (SearchSQLServer.com) query  (SearchSQLServer.com) SQL  (SearchSQLServer.com) T-SQL  (SearchSQLServer.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

option 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