Solved

CHECKDB job keeps failing.

Posted on 2009-04-02
3
435 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
  • 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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now