Problem
We recently moved all our SharePoint databases to a new server. This morning I noticed that the new SQL server was reporting that one of the databases had a corrupted index.
Log Name: Application
Source: MSSQL$SP
Date: 2/01/2013 9:05:02 AM
Event ID: 8646
Task Category: Server
Level: Error
Keywords: Classic
User: domain\user
Computer: db01.server.com
Description:
Unable to find index entry in index ID 3, of table 1618104805, in database ‘Profile DB’. The indicated index is corrupt or there is a problem with the current update plan. Run DBCC CHECKDB or DBCC CHECKTABLE. If the problem persists, contact product support.
Event Xml:
Running a dbcc checkdb on the database showed that there were many consistency errors in one of the table indexes.
Solution
To repair the database, I used the following T-SQL script. This script will set your database to single user mode, run the repair, and set is back to multi-user mode.
Open SQL Management Studio
Click on New Query and paste the following script, changing ‘db name’ to the name of your database.
Use [db name] Alter Database [db name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE go dbcc checkdb('db name', repair_rebuild) go Alter Database [db name] SET MULTI_USER |
Once the script completes successfully, run a dbcc checkdb on the database again to see if there are any more errors.
Hello Dear,
I had tried your suggestion but unable to fix index corruption then after dropped & re-create the index of database.
T-SQL to drop an Index: DROP INDEX Index-name ON Table-name;
T-SQL to re-create an Index: CREATE INDEX index_name ON table_name (column_name);
There are some in-build utility also that repair corrupt indexes of database.
Great stuff thanks for sharing ! Repairing a corrupted index in a database is beginning to clear up. As i found a helpful free SQL MDF recovery tool that fixes all the corrupted Index and other database objects http://www.mdfviewer.com/recovery/