DBCC REPAIR_REBUILD fails - help required.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Data Corruption Issues

DBCC REPAIR_REBUILD fails - help required.

Author

Topic

dherd
Starting Member

4 Posts

Posted - 2005-10-24 : 07:21:29

I have a customer who has reported some database corruption. The output of
DBCC CHECKDB ('CheckDB') is as follows:

Server: Msg 8935, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. The previous link (1:76577)
on page (1:76578) does not match the previous page (1:253212) that the parent
(1:64277), slot 165 expects for this page.
Server: Msg 8936, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. B-tree chain linkage
mismatch. (1:253212)->next = (1:76578), but (1:76578)->Prev = (1:76577).
Server: Msg 8934, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. The high key value on page
(1:253212) (level 0) is not less than the low key value in the parent (0:1),
slot 0 of the next page (1:76578).
Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. Page (1:253212) is missing a
reference from previous page (1:76577). Possible chain linkage problem.
CHECKDB found 0 allocation errors and 4 consistency errors in table
'Student' (object ID 1685581043).
CHECKDB found 0 allocation errors and 4 consistency errors in database
'CheckDB'.
repair_rebuild is the minimum repair level for the errors found by DBCC
CHECKDB (LincsDB ).

(1 row(s) affected)

Attempting a DBCC CHECKDB ('CheckDB', REPAIR_REBUILD) yields the following
results:

Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index
ID 2. Most significant primary key is '312037'.
Server: Msg 8935, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. The previous link (1:76577)
on page (1:76578) does not match the previous page (1:253212) that the parent
(1:64277), slot 165 expects for this page.
Server: Msg 8936, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. B-tree chain linkage
mismatch. (1:253212)->next = (1:76578), but (1:76578)->Prev = (1:76577).
Server: Msg 8934, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. The high key value on page
(1:253212) (level 0) is not less than the low key value in the parent (0:1),
slot 0 of the next page (1:76578).
Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. Page (1:253212) is missing a
reference from previous page (1:76577). Possible chain linkage problem.
Could not repair this error.
Repairing this error requires other errors to be corrected first.
Repairing this error requires other errors to be corrected first.
Repairing this error requires other errors to be corrected first.
CHECKDB found 0 allocation errors and 4 consistency errors in table
'Student' (object ID 1685581043).
CHECKDB found 0 allocation errors and 4 consistency errors in database
'LincsDB'.
repair_rebuild is the minimum repair level for the errors found by DBCC
CHECKDB (LincsDB repair_rebuild).
The statement has been terminated.

(1 row(s) affected)

I can resolve the issue by dropping and recreating index id: 2 but this
involves dropping and re-creating a lot or related constraints (this is a
primary key).

Can anyone give me any pointers as to why this corruption may have occurred
or more information about the kind of corruption? I have not seen this on any
other customer databases so I don't think it is an application error? Also,
any suggestions on a better (simpler) way to correct this problem?

Unfortunatley, the customers last viable backup is a couple of weeks out of
date and they are reluctant to restore and re-enter all the new data again.

Any help greatly appreciated,
Dave.

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-10-24 : 07:52:54

Hi Dave,

Firstly, its not possible for an application to cause physical data corruption - no matter how hard it tries.

The actual corruption is in the clustered index. Rebuilding it is causing the non-clustered indexes to be rebuilt (which leads me to believe the customer isn't on SP3 or SP4?). You're going to have to delete the record which has duplicate values for the key for index id 2, and then rebuild the clustered index. Just dropping index id 2 won't cut it - because you won't be able to create it again without deleting said record.

Corruption can happen through two means - h/w problems or a SQL Server bug, and its nearly always caused by the former. Can you check :
- the SQL Server errorlogs and Window event logs for evidence of h/w
problems (failed IOs for instance)?
- the IO drivers are up-to-date
- the IO firmware is all up-to-date
- that IO sub-system diagnostics are clean
- that memory diagnostics are clean

Can you also tell me which version they're on?

I take it you'll be educating them about why its necessary for them to have a sound, tested disaster recovery strategy for the next time something like this happens to them?

Thanks

Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)

dherd
Starting Member

4 Posts

Posted - 2005-10-24 : 09:55:14

Hi Paul,

Thanks for the prompt reply. Great to hear that it can't be our software which has caused this corruption.

I have dropped the primary key index and as you point out, it is not possible to re-create the index and an error is generated highlighting duplicate key values. I am assuming that these values should be in the column on which I am trying to re-create the index but I am not sure how to find out which values are duplicated. Running a query on the column does not highlight any duplicate values. Should I be looking at something other than the column values?

I have passed your suggestions for identifying the root cause of the problem to the customer and requested details of the exact version of SQL Server they are using and will post details here when they become available. Will also recommend they apply SP4 if it's not there yet.

I have also suggested that the add a regular run of DBCC CHECKDB to their maintenance plan to highlight any future issues much sooner.

Thanks,
Dave.

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-10-24 : 11:03:17

You're welcome.

Look at the error message:

Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index
ID 2. Most significant primary key is '312037'.

Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)

dherd
Starting Member

4 Posts

Posted - 2005-10-24 : 12:21:53

Hi Paul,

I had checked for this duplicate but there is only a single record in this table with the specified value. I was expecting multiple records sharing the same value of which I could simply delete the duplicates. I was trying to use a query such as:

SELECT StudentID, COUNT(*) FROM Student GROUP BY StudentID HAVING COUNT(*) > 1

to identify the duplicates in the database but this does not return any duplicates. Do I have to go through them one by one trying to recreate the index after deleting each record or is there a way to identify all the duplicates in one go?

Have also had some version information back from the customer as follows:

Microsoft SQL Server 7.00 - 7.00.1063 (Intel X86) Apr 9 2002
14:18:16 Copyright (c) 1988-2002 Microsoft Corporation Standard
Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

I have a copy of the database on SQL Server 2000 Developer Edition with SP4 installed and experience the same issues reported by the customer.

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-10-24 : 12:34:49

You experience the same corruption? Did you restore a backup of the 7.0 database? If so, that's what I'd expect you to see. If not, what did you do?

What's the definition of the index key? What are the messages that CREATE INDEX returns to you?

Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)

dherd
Starting Member

4 Posts

Posted - 2005-10-25 : 11:01:33

Hi Paul,

Sorry for the misunderstanding, I restored a copy of their database so would expect to see the same problems.

I had to drop another index on the tables before I could identify the duplicates using a query and delete one of the duplicate records. I have now successfully produced a script which drops all the relevant constraints, identifies and deletes the duplicate keys and then re-creates all the constraints without loss of the original data (we think :-)). Should make for a very relieved customer.

Thanks again for all your help with this problem.

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-10-25 : 13:24:19

Happy ending - glad to hear it. Post again if you have any further problems.

Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources