sql server Keys out of order on page

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

sql server Keys out of order on page

Author

Topic

carthik_s
Starting Member

34 Posts

Posted - 2006-02-14 : 12:08:26

I am getting the following error in the database..

B-tree chain linkage mismatch
Keys out of order on page

I have run the DBCC Repair_rebuild with data_loss.
But this has not helped to resolve the issue. It is finding errors in the Primary Key Linkage.

Please assist.
Thanks for your help.
Karthik

carthik_s
Starting Member

34 Posts

Posted - 2006-02-14 : 12:13:54

Hi,

This is pretty urgent. Any help is appreciated a lot.

Thanks a lot SQL Team.

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2006-02-14 : 12:42:34

Please post the output from:

DBCC CHECKDB (yourdb) WITH NO_INFOMSGS, ALL_ERRORMSGS

Please also post:
1) sql version and SP level
2) when you started seeing the errors
3) your backup strategy

Why did you run repair?

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

carthik_s
Starting Member

34 Posts

Posted - 2006-02-14 : 13:02:43

Hi Paul,

Thanks for writing. I am currently running the DBCC on the database.
The following are the answers to the other questions:-

1) Sql Server is 2000 on windows 2000 box. The SP Level is
Build 2195: Service Pack 4
2)We started to see errors two days back.
3)The back up plan is daily and we store a weeks data.
But from the day the issue stared, we can not even create a backup. The job that creates the back up is also failing.

We run the repair because when I tried to fetch a particular row using query analyser, I got a fatal error message as below:-

"Server: Msg 21, Level 22, State 1, Line 1
Warning: Fatal error 8908 occurred at Feb 14 2006 10:58AM

Connection Broken"

carthik_s
Starting Member

34 Posts

Posted - 2006-02-14 : 13:06:23

DBCC Output:-

Server: Msg 8935, Level 16, State 1, Line 1
Table error: Object ID 165575628, index ID 1. The previous link (1:460535) on page (1:460536) does not match the previous page (1:460913) that the parent (1:461100), slot 98 expects for this page.
Server: Msg 8936, Level 16, State 1, Line 1
Table error: Object ID 165575628, index ID 1. B-tree chain linkage mismatch. (1:460909)->next = (1:460536), but (1:460536)->Prev = (1:460535).
Server: Msg 8935, Level 16, State 1, Line 1
Table error: Object ID 165575628, index ID 1. The previous link (1:460913) on page (1:460537) does not match the previous page (1:460536) that the parent (1:461100), slot 99 expects for this page.
Server: Msg 8934, Level 16, State 1, Line 1
Table error: Object ID 165575628, index ID 1. The high key value on page (1:460909) (level 0) is not less than the low key value in the parent (0:1), slot 0 of the next page (1:460536).
Server: Msg 8935, Level 16, State 1, Line 1
Table error: Object ID 165575628, index ID 1. The previous link (1:460536) on page (1:460913) does not match the previous page (1:460909) that the parent (1:461100), slot 97 expects for this page.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 165575628, Index ID 1. Keys out of order on page (1:461100), slots 97 and 98.
CHECKDB found 0 allocation errors and 6 consistency errors in table 'CC_TRANSACTION' (object ID 165575628).
CHECKDB found 0 allocation errors and 6 consistency errors in database
repair_rebuild is the minimum repair level for the errors found by DBCC CHECKDB.

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2006-02-14 : 13:20:50

How was the backup job failing and what did you do to fix it?

What was the output from CHECKDB when you ran repair? Which option did you use? You said 'repair_rebuild with data_loss' bu these are two seperate options.

What happens if you try manually rebuilding index id 1 of object_name(165575628)?

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

carthik_s
Starting Member

34 Posts

Posted - 2006-02-14 : 13:30:17

The back up job is failing giving the Fatal Error 8908 on the table.

We used DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS.

We have not tried to rebuild the Index id 1 since it is primary key. The RI might get messed up, if we do something with the Primary Key. Do you think we should rebuild the primary key?

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2006-02-14 : 14:17:35

I'm confused - you're worried about rebuilding the index because it might (will probably) screw-up RI, yet you ran (the deliberately named) REPAIR_ALLOW_DATA_LOSS without knowing what it would do and against the suggestion of CHECKDB?

What was the output when you ran repair?

Do you mean that you got an 8908 error from a BACKUP command? Or the backup job? I think you mean the latter. What else apart from BACKUP does your backup job do?

I think you may lose (or already have lost) data so you should restore from your last known good backup. Alternatively, you could rebuild the index and run DBCC CHECKCONSTRAINTS to work out which data has broken RI adn fix them up manually. I recommend the former.

Thanks

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

carthik_s
Starting Member

34 Posts

Posted - 2006-02-14 : 14:24:30

Ok.. Let me work on Recreating the Index. I will let you know what was the result

carthik_s
Starting Member

34 Posts

Posted - 2006-02-14 : 14:37:02

Hi Paul,
Should be post the steps for deleting the primary key and rebuiliding it? I am not sure how to drop the RIs?
Thanks for your replies.

carthik_s
Starting Member

34 Posts

Posted - 2006-02-14 : 14:37:38

Hi Paul,
Can you post the steps for deleting the primary key and rebuiliding it? I am not sure how to drop the RIs?
Thanks for your replies.

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2006-02-14 : 14:50:55

You need to call Product Support - I can't walk you through that over this medium with any guarantee of success.

Thanks

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

carthik_s
Starting Member

34 Posts

Posted - 2006-02-14 : 18:23:37

Hi Paul,

I got the following error in the back up:-

Error at Source for row number 3979486. Erros Encountered so far in this task: 1
Table Error: Database ID 11, object ID 165575628, Index ID 0. Chain Linkage mismatch. (1:460909)->next=(1:460536), but (1:460536)->prev=(1:460535)

Can you think of something?

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2006-02-14 : 18:56:04

What is your 'back up' task doing? It's not doing a SQL BACKUP, that's for sure. Looks like some kind of data export. Can you give more details?

Whatever its doing, its hitting the corruption in that index and will not proceed past it.

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

carthik_s
Starting Member

34 Posts

Posted - 2006-02-14 : 19:38:42

The backup job runs a DTS package and exports data to another table.

carthik_s
Starting Member

34 Posts

Posted - 2006-02-14 : 19:42:51

Paul,

I ran the following on the db.
DBCC TRACEON (3604)
DBCC PAGE (TR, 1, 460536, 3)

DBCC TRACEON (3604)
DBCC PAGE (TR, 1, 460535, 3)

DBCC TRACEON (3604)
DBCC PAGE (TR, 1, 460909, 3)

The issue is as below:-
1:460909 has sequence number (which is the primary key) from 89 to 96.
1:460535 has sequence number (which is the primary key) from 71 to 79.
1:460536 has sequence number (which is the primary key) from 89 to 97.

Issue is
Chain linkage mismatch. (1:460909)->next = (1:460536), but (1:460536)->prev = (1:460535)

Bit confusing for me. Can two pages have same data (as in data for 460909 and 460536 are overlapping)?

I appreciate your help.
Thanks.

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2006-02-14 : 21:07:58

No - that's the problem. Do you have any real backups (i.e. of the whole database)?

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

carthik_s
Starting Member

34 Posts

Posted - 2006-02-14 : 21:56:56

Yep.. I do have a backup of the database.

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2006-02-14 : 22:36:02

In that case, you should restore that and then you don't have to worry about fixing RI problems. How old is it?

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

carthik_s
Starting Member

34 Posts

Posted - 2006-02-15 : 11:44:52

It is two days old. But the data for the past two days would be lost, isnt it?

carthik_s
Starting Member

34 Posts

Posted - 2006-02-15 : 11:47:04

I ran DBCC Repair_rebuild again yesterday, and got the following error:-

Server: Msg 8935, Level 16, State 1, Line 1
Table error: Object ID 165575628, index ID 1. The previous link (1:460913) on page (1:460537) does not match the previous page (1:460536) that the parent (1:461100), slot 99 expects for this page.
Server: Msg 8934, Level 16, State 1, Line 1
Table error: Object ID 165575628, index ID 1. The high key value on page (1:460909) (level 0) is not less than the low key value in the parent (0:1), slot 0 of the next page (1:460536).
Server: Msg 8935, Level 16, State 1, Line 1
Table error: Object ID 165575628, index ID 1. The previous link (1:460536) on page (1:460913) does not match the previous page (1:460909) that the parent (1:461100), slot 97 expects for this page.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 165575628, Index ID 1. Keys out of order on page (1:461100), slots 97 and 98.
DBCC results for 'TCPaylinxuser5.CC_TRANSACTION'.
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.
Repairing this error requires other errors to be corrected first.
Repairing this error requires other errors to be corrected first.
Could not repair this error.
There are 3993052 rows in 267714 pages for object 'TRANSACTION'.
CHECKTABLE found 0 allocation errors and 6 consistency errors in table 'TRANSACTION' (object ID 165575628).
repair_rebuild is the minimum repair level for the errors found by DBCC CHECKTABLE (TRANSACTION repair_rebuild).
The statement has been terminated.

Any suggestion? If nothing can be done, I would restored the backup.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources