DBCHECK: Extra or invalid key - SQL Server Forums

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

DBCHECK: Extra or invalid key

Author

Topic

thmthm
Starting Member

3 Posts

Posted - 2005-07-22 : 08:29:54

Hi

I've also got an inconsistency problem!

My CHECKDB says:

Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'Multiflex_YBS', index 'mf_InvObject.I_mf_InvObject_Archived' (ID 308196148) (index ID 2). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:1189627:69) with values (Archived = Jul 6 2005 10:04PM and InvObjectId = 4374699) points to the data row identified by ().

"Extra or invalid key" ? How can that happen?

Kind regards,
Thomas Holmgren
Denmark

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2005-07-22 : 08:44:25

probably an application bug with your front end.

Sean Roussy

Please backup all of your databases including master, msdb and model on a regular basis. I am tired of telling people they are screwed. The job you save may be your own.

I am available for consulting work. Just email me through the forum.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-22 : 08:47:53

something similar to this
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=51984

Madhivanan

Failing to plan is Planning to fail

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-07-23 : 11:39:20

quote:
Originally posted by Thrasymachus

probably an application bug with your front end.

Sean Roussy

Not possible ever for an app bug to cause database corruption - no matter how hard it tries.

Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-07-23 : 11:41:17

quote:
Originally posted by thmthm

Hi

I've also got an inconsistency problem!

My CHECKDB says:

Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'Multiflex_YBS', index 'mf_InvObject.I_mf_InvObject_Archived' (ID 308196148) (index ID 2). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:1189627:69) with values (Archived = Jul 6 2005 10:04PM and InvObjectId = 4374699) points to the data row identified by ().

"Extra or invalid key" ? How can that happen?

Kind regards,
Thomas Holmgren
Denmark

Is this the only error that CHECKDB returns? This says there's an extra row in a non-clustered index that doesn't match any row in the heap/clustered-index.

What version are you on?

Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine

thmthm
Starting Member

3 Posts

Posted - 2005-08-17 : 05:36:46

quote:
Originally posted by paulrandal
Is this the only error that CHECKDB returns? This says there's an extra row in a non-clustered index that doesn't match any row in the heap/clustered-index.

What version are you on?

Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine

Hi Ralph,

The SQL server causing problems runs at one of our customers, a larger British food-processing company. They're using SQL Server 2000 with all current updates installed. The table causing the error contains approx. 5.000.000 tuples. The DB processes about 20 transactions/sec. (avg) plus some reporting-queries now and then.

CHECKDB reports a bunch of index-inconsistency errors like the one above. REPAIR_ALLOW_DATA_LOSS fixes the problem, but after a while the errors re-appear and after a week or so the DB crashes. We've checked the hardware multiple times, tried dropping and re-creating the tables and indices but nothing helps.

The other day we did a backup and restore (which rebuilds the indices and thus removes the error). Afterwards CHECKDB reported 18 inconsistencies. What's funny is that after a few hours CHECKDB reported 0 errors - we hadn't touched the DB at all. Now there's a lot of errors again, and we'll do a backup/restore to prevent the DB from going down.

I would be sure it was a hardware error if we hadn't tried replacing the entire server with an identical one.

Some of the stored procedures used for providing data for varios reports use the WITH NOLOCK-option. I've heard people say that using the NOLOCK option can cause CHECKDB report this kind of errors? Does CHECKDB read un- or partial comitted transactions?

Hope I'll find a solution soon! :)

Kind regards,
Thomas Holmgren
Denmark

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-08-17 : 10:51:18

Who's this Ralph guy posing as me?

NOLOCK has no effect whatsoever on CHECKDB.

This sounds like you've hit a bug in non-clustered index maintenance. It also sounds like the backup you restored contained corrupt indexes. Your best bet is to contact PSS to help you.

Thanks

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

thmthm
Starting Member

3 Posts

Posted - 2005-08-18 : 03:39:12

quote:
Originally posted by paulrandal

Who's this Ralph guy posing as me?
This sounds like you've hit a bug in non-clustered index maintenance. It also sounds like the backup you restored contained corrupt indexes. Your best bet is to contact PSS to help you.

Hi Paul,

I'm sorry Paul, I was just mailing a guy called Ralph and got my keystrokes mixed up. :)

I have just ONE more question before I call MS PSS (or buy an Oracle license) ;) : When doing a backup/restore, are the indices rebuild on restore or is index data backed up as well?? When you say "corrupt index" do you mean corrupt index data or index definitions? Dropping and re-creating the indices should create correct indices (i.e. "non-corrupt" index defs.) I suppose?

Hm, that was three questions after all. Thanks Paul, I really appreciate your super-qualified posts in here! :)

Kind regards,
Thomas Holmgren
Denmark

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-08-18 : 10:46:15

quote:
Originally posted by thmthm

quote:
Originally posted by paulrandal

Who's this Ralph guy posing as me?
This sounds like you've hit a bug in non-clustered index maintenance. It also sounds like the backup you restored contained corrupt indexes. Your best bet is to contact PSS to help you.

Hi Paul,

I'm sorry Paul, I was just mailing a guy called Ralph and got my keystrokes mixed up. :)

No worries - I've been called a lot worse. At present some wag's replaced my office nameplate with 'Severus Snape' (yes, I look like him in the movies but without the cape and wand

)

quote:

I have just ONE more question before I call MS PSS (or buy an Oracle license) ;) : When doing a backup/restore, are the indices rebuild on restore or is index data backed up as well??

The index data is backed up. Backup has virtually no idea what extents its backing up (it backs up 8 pages at a time, even if only one has been changed, to get better IO throughput) apart from those containing allocation bitmaps.

quote:

When you say "corrupt index" do you mean corrupt index data or index definitions?

Corrupt index data (i.e. an index row that does not match any row in the heap/clustered index - an extra or corrupt row, as in your case - or heap/clustered index row that does not have a matching index row - missing or corrupt row)

quote:

Dropping and re-creating the indices should create correct indices (i.e. "non-corrupt" index defs.) I suppose?

Dropping and recreating an index does create a new index definition but that's not the issue here. Its the fact the the index rows are regenerated from the heap/clustered index that resolves the inconsistencies - as long as there isn't a bug in that algorithm.

Doing seperate drop/create statements can lead to problems if a uniqueness constraint, for example, becomes invalid between the drop and create statements. Far better to do a plain rebuild or CREATE INDEX .. WITH DROP_EXISTING

quote:

Hm, that was three questions after all. Thanks Paul, I really appreciate your super-qualified posts in here! :)

Kind regards,
Thomas Holmgren
Denmark

You are most welcome. I'm interested to hear how you get on with PSS.

Thanks and regards.

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

cmayil
Starting Member

1 Post

Posted - 2005-08-26 : 16:03:09

Guys,
I am also having the same issue in our Server.It is happening very often.Here is the error message.
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'callcenteradmin', index 'AGENT_ACTIVITY.XIE2AGENT_ACTIVITY' (ID 523148909) (index ID 6). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (3:475193:95) with values (CAMP_ABBR = 'ACNR0085' and CURRDATE = '050826' and DISPOSITION_CODE = '90' and UNIQUENUM = '168980' and AGENT_INITIALS = 'cl7' and CAMP_ABBR = 'ACNR0085' and UNIQUENUM = '168980' and ? = 1) points to the data row identified by ().
DBCC results for 'AGENT_ACTIVITY'.
There are 8870419 rows in 256475 pages for object 'AGENT_ACTIVITY'.
CHECKTABLE found 0 allocation errors and 1 consistency errors in table 'AGENT_ACTIVITY' (object ID 523148909).
repair_fast is the minimum repair level for the errors found by DBCC CHECKTABLE (callcenteradmin.dbo.AGENT_ACTIVITY ).

After i rebuild the index , the problem is fixed.But it is keep happening.Anybody found solution for this problme.

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-08-27 : 10:43:31

Looks like you've hit the same bug and you need to call PSS to help you out (http://support.microsoft.com)

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