Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
also, as for the ERRORLOG issue: connect.microsoft.com/SQLServer/feedback/details/468187/… –  swasheck Dec 3 '13 at 20:37
    
@ Dean, The errorlog message is informational. The CHECKTABLE command needs a consistent view of the table. If you are not in single user mode it will create a snapshot of the database in the background. The message in the errorlog is just telling you at what point in time the snapshot was taken. –  Edward Dortland Dec 5 '13 at 11:18
    
Also, the MERGE command would most likely not have caused your corruption. It could be that corruption was revealed because the MERGE statement requested a page that had a problem and the page failed the checksum check. So the corruption was detected at that point. But the corruption might have been created earlier. Have you ran just CHECKTABLE? Might be good to run a CHECKDB to see the state of the whole db. And try and find the root cause of why the page got corrupted. –  Edward Dortland Dec 5 '13 at 11:24
    
@Edward Dortland I don't think the merge caused the corruption but I think some how the index got corrupted. The merge in the log just let me know what table to look at for the problem. The database is only read by users and written to by automated processes which I stopped for the check so, for me, I think I'm ok without single user check –  Dean MacGregor Dec 6 '13 at 13:30

1 Answer 1

up vote 1 down vote accepted

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).

share|improve this answer
1  
I think this did the trick. I reran DBCC CHECKTABLE after deleting the index and it reported no error. –  Dean MacGregor Dec 3 '13 at 21:52
    
@DeanMacGregor awesome. –  swasheck Dec 3 '13 at 21:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.