Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

CHECKDB job keeps failing.

Posted on 2009-04-02
3
Medium Priority
?
456 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:Richard Comito
  • 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:Richard Comito
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:Richard Comito
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

564 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