Sql-server – sql server 2008 r2 DBCC checktable error

corruptionsql serversql-server-2008-r2

This morning my database went into suspect mode. I reverted back to last night's full backup to get it back online. I went through the error log and found the last command that appeared to cause the suspect mode was on a MERGE on a particular table. I ran DBCC CHECKTABLE on the table and got back the following 

Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1253579504, index ID 7, partition ID 72057594230341632, alloc unit ID 72057594364887040 (type In-row data). Keys out of order on page (1:14254237), slots 184 and 185.
There are 366539002 rows in 3425599 pages for object "PJM_Prices".
CHECKTABLE found 0 allocation errors and 1 consistency errors in table 'PJM_Prices' (object ID 1253579504).
repair_rebuild is the minimum repair level for the errors found by DBCC CHECKTABLE (CaisoAnalyzer.dbo.PJM_Prices).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

in ERRORLOG

Internal database snapshot has split point LSN = 001935ad:00001bf6:0001 and first LSN = 001935ad:00001bf5:0001

Is there possibly a way to put just that table in single user mode or some other trick where I don't have to go into single user mode for the whole database?

Best Answer

One suggestion would be to identify the index that is causing the problem.

select o.name as tablename, i.name as indexname 
  from sys.objects o 
  join sys.indexes i 
    on o.object_id = i.object_id 
  where i.index_id = 7 
    and o.object_id = 1253579504

and then drop it. If you believe you're going to need this index then I'd script out the DDL before dropping it, drop it, recreate it, and then rerun your DBCC. Since its index_id is 7 I'm going to assume that this table is potentially over-indexed so YMMV with actually recreating it.

If dropping the index doesn't work, then I would always recommend a last-known-good backup to which you can restore. Also, if this has been occurring for a while, the backup will also have the corrupted index since a full backup is a page-by-page copy of the database (+ some transaction log).

Related Question