Solved

CHECKDB job keeps failing.

Posted on 2009-04-02
3
443 Views
Last Modified: 2012-05-06
I do a nightly CHECKDB on my Database.  But for the past 3 nights it has been failing with the following message in the history.

Executed as user: NT AUTHORITY\SYSTEM. ...a row does not have a matching index row in the index 'IND_T_ORDER_SEAT_HIST_batch_no' (ID 2). Possible missing or invalid keys for the index row matching: [SQLSTATE 42000] (Error 8951)  Data row (1:330885:0) identified by (event_date = '2006-11-13 12:20:02.313' and perf_no = 486 and UNIQUIFIER = 1) with index values 'batch_no = 18255 and event_date = '2006-11-13 12:20:02.313' and perf_no = 486 and UNIQUIFIER = 1'. [SQLSTATE 42000] (Error 8955)  Table error: table 'T_ORDER_SEAT_HIST' (ID 1330103779). Data row does not have a matching index row in the index 'IND_T_ORDER_SEAT_HIST_batch_no' (ID 2). Possible missing or invalid keys for the index row matching: [SQLSTATE 42000] (Error 8951)  Data row (1:330885:1) identified by (event_date = '2006-11-13 12:20:02.377' and perf_no = 486 and UNIQUIFIER = 0) with index values 'batch_no = 18255 and event_date = '2006-11-13 12:20:02.377' and perf_no = 486 and UNIQUIFIER = 0'. [SQLSTATE 42000] (Error 8955)  ...  The step failed.


I have ran UP_REBUILD_ALL_INDEXES on the table that is failing but it does not seem to help.  What would be the best way for me to address this issue?

Thanks,

Gabicus
0
Comment
Question by:GabicusC
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 8

Accepted Solution

by:
vinurajr earned 500 total points
ID: 24051742
That's a lot easier.

Ok, this is not a good situation. You have corruption that cannot be repaired without losing data. Since you have no clean backups, we're going to have to repair or rebuild MSDB completely.

You have two options.

You can run Checkdb with the repair_allow_data_loss option. This will delete data from five tables: backupfile, backupmediaset, backupmediafamily, sysjobhistory, backupset.
I don't know what problems this could cause with backups in the future.

You can script out all your jobs and then search for the article on MSDN that explains how to rebuild MSDB. Once rebuilt you can run all the scripted jobs to get the jobs back.
This is more work, but it is safer.

Your choice.

Once you have MSDB clean, check the windows event logs to see if you can find anything that may have cause this. Typically corruption is due to IO issues.
Put a job in place to do regular consistency checks on all databases (except TempDB) So that if this happens again you can catch it early.


--------------------------------------------------------------------------------
Gail Shaw


We walk in the dark places no others will enter
We stand on the bridge and none may pass

courtsey:http://www.sqlservercentral.com/Forums/Topic653669-265-1.aspx

http://social.technet.microsoft.com/Forums/en-US/sqldatabaseengine/thread/b74dcdd3-a492-4b60-931e-a5223ddf7e68/
0
 

Author Comment

by:GabicusC
ID: 24061293
vinurajr,

Thanks for the reply.  I ran DBCC DBREINDEX ('t_order_seat_hist', IND_T_ORDER_SEAT_HIST_batch_no)

just on the index that was giving my an issue and then when I ran the CheckDB all came back nice and clean.  I am going to start with the consistency check to stay on top of the issue and will need to run a check drive to see if there is a bad IO.  however I am not sure if I can run that on a RAID.  But that is a whole new string.
0
 

Author Closing Comment

by:GabicusC
ID: 31565865
I was able to run DBCC DBREINDEX ('t_order_seat_hist', IND_T_ORDER_SEAT_HIST_batch_no)  to fix the issue without rebuilding the whole MSDB.  But this Answer did give some good explanation of what was going on and other things I can check to head this problem off in the future.
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question