SQL – Repairing a corrupted index in a database

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.

Tags:

2 Responses to “SQL – Repairing a corrupted index in a database”

  1. markwillium January 18, 2013 at 2:13 pm #

    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.

  2. johnson Welch August 4, 2016 at 5:18 pm #

    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/

Leave a Reply

%d bloggers like this: