Solved

CHECKDB job keeps failing.

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

772 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