?
Solved

CHECKDB job keeps failing.

Posted on 2009-04-02
3
Medium Priority
?
449 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 1500 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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

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.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.
Suggested Courses

777 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