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.