SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Corrupted index - find out what caused it


Corrupted index - find out what caused it

Author
Message
terry999
terry999
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4171 Visits: 764
Hi

I got this error on a scheduled integrity check. SQL 2005 sp2

Data row does not have a matching index row in the index '' (ID 2). Possible missing or invalid keys for the index row matching:

The corrupted index is a non-clustered Unique index on a VIEW.

I rebuild both the clustered index on the view and the corrupted index.
It fixed the problem.

I'm trying to find what caused the corruption.
1. Windows Event viewer shows nothing
2. SQL Error Log shows no error.

I'm surprised the DB was not being used in the period it failed (weekend).


Any ideas?
Alan Burstein
Alan Burstein
SSC Guru
SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)

Group: General Forum Members
Points: 53628 Visits: 8955
terry999 (5/2/2016)
Hi

I got this error on a scheduled integrity check. SQL 2005 sp2

Data row does not have a matching index row in the index '' (ID 2). Possible missing or invalid keys for the index row matching:

The corrupted index is a non-clustered Unique index on a VIEW.

I rebuild both the clustered index on the view and the corrupted index.
It fixed the problem.

I'm trying to find what caused the corruption.
1. Windows Event viewer shows nothing
2. SQL Error Log shows no error.

I'm surprised the DB was not being used in the period it failed (weekend).


Any ideas?


Logging and the event viewer could shine some light on this but that depends on what's logged and for how long. Many things can cause corruption.

-- Alan Burstein


Helpful links:

Best practices for getting help on SQLServerCentral -- Jeff Moden
How to Post Performance Problems -- Gail Shaw

Nasty fast set-based string manipulation functions:
For splitting strings try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL Server 2012+)
To split strings based on patterns try PatternSplitCM
Need to clean or transform a string? try NGrams, PatExclude8K, PatReplace8K, DigitsOnlyEE, or Translate8K

I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. -- Itzik Ben-Gan 2001

Luis Cazares
Luis Cazares
SSC Guru
SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)

Group: General Forum Members
Points: 160659 Visits: 22697
Alan.B (5/2/2016)
terry999 (5/2/2016)
Hi

I got this error on a scheduled integrity check. SQL 2005 sp2

Data row does not have a matching index row in the index '' (ID 2). Possible missing or invalid keys for the index row matching:

The corrupted index is a non-clustered Unique index on a VIEW.

I rebuild both the clustered index on the view and the corrupted index.
It fixed the problem.

I'm trying to find what caused the corruption.
1. Windows Event viewer shows nothing
2. SQL Error Log shows no error.

I'm surprised the DB was not being used in the period it failed (weekend).


Any ideas?


Logging and the event viewer could shine some light on this but that depends on what's logged and for how long. Many things can cause corruption.

As I've been explained, most of the times is the file system.


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Alan Burstein
Alan Burstein
SSC Guru
SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)

Group: General Forum Members
Points: 53628 Visits: 8955
Luis Cazares (5/3/2016)
Alan.B (5/2/2016)
terry999 (5/2/2016)
Hi

I got this error on a scheduled integrity check. SQL 2005 sp2

Data row does not have a matching index row in the index '' (ID 2). Possible missing or invalid keys for the index row matching:

The corrupted index is a non-clustered Unique index on a VIEW.

I rebuild both the clustered index on the view and the corrupted index.
It fixed the problem.

I'm trying to find what caused the corruption.
1. Windows Event viewer shows nothing
2. SQL Error Log shows no error.

I'm surprised the DB was not being used in the period it failed (weekend).


Any ideas?


Logging and the event viewer could shine some light on this but that depends on what's logged and for how long. Many things can cause corruption.

As I've been explained, most of the times is the file system.


Yep. That has been my experience as well.

-- Alan Burstein


Helpful links:

Best practices for getting help on SQLServerCentral -- Jeff Moden
How to Post Performance Problems -- Gail Shaw

Nasty fast set-based string manipulation functions:
For splitting strings try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL Server 2012+)
To split strings based on patterns try PatternSplitCM
Need to clean or transform a string? try NGrams, PatExclude8K, PatReplace8K, DigitsOnlyEE, or Translate8K

I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. -- Itzik Ben-Gan 2001

gracechristopher06
gracechristopher06
SSC Veteran
SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)

Group: General Forum Members
Points: 259 Visits: 26
Fixing corrupted indexes

If the index on one of your system tables has been corrupted, you can use the sp_fixindex system procedure to repair the index. For syntax information, see the entry for sp_fixindex in “System Procedures” in the Adaptive Server Reference Manual.

StepsRepairing the system table index with sp_fixindex

Get the object_name, object_ID, and index_ID of the corrupted index. If you only have a page number and you need to find the object_name, see the Adaptive Server Troubleshooting and Error Messages Guide for instructions.

If the corrupted index is on a system table in the master database, put Adaptive Server in single-user mode. See the Adaptive Server Troubleshooting and Error Messages Guide for instructions.

If the corrupted index is on a system table in a user database, put the database in single-user mode and reconfigure to allow updates to system tables:

1> use master
2> go

1> sp_dboption database_name, "single user", true
2> go

1> sp_configure "allow updates", 1
2> go

Issue the sp_fixindex command:

1> use database_name
2> go1> checkpoint
2> go1> sp_fixindex database_name, object_name, index_ID
2> go

You can use the checkpoint to identify the one or more databases or use an all clause.

checkpoint [all | [dbname[, dbname[, dbname.....]]]

NoteYou must be assigned sa_role to run sp_fixindex.

Run dbcc checktable to verify that the corrupted index is now fixed.

Disallow updates to system tables:

1> use master
2> go1> sp_configure "allow updates", 0
2> go

Turn off single-user mode:

1> sp_dboption database_name, "single user", false
2> go1> use database_name
2> go1> checkpoint
2> go

You can use the checkpoint to identify the one or more databases or use an all clause, which means you do not have to issue the use database command.

checkpoint [all | [dbname[, dbname[, dbname.....]]]

StepsRepairing a nonclustered index on sysobjects

Perform steps 1 – 3, as described in “Repairing the system table index with sp_fixindex,” above.

Issue:

1> use database_name
2> go1> checkpoint
2> go1> select sysstat from sysobjects
2> where id = 1
3> go

You can use the checkpoint to identify the one or more databases or use an all clause.

checkpoint [all | [dbname[, dbname[, dbname.....]]]

Save the original sysstat value.

Change the sysstat column to the value required by sp_fixindex:

1> update sysobjects
2> set sysstat = sysstat | 4096
3> where id = 1
4> go

Run:

1> sp_fixindex database_name, sysobjects, 2
2> go

Restore the original sysstat value:

1> update sysobjects
2> set sysstat = sysstat_ORIGINAL
3> where id = object_ID
4> go

Run dbcc checktable to verify that the corrupted index is now fixed.

Disallow updates to system tables:

1> sp_configure "allow updates", 0
2> go

Turn off single-user mode:

1> sp_dboption database_name, "single user", false
2> go1> use database_name
2> go1> checkpoint
2> go

You can use the checkpoint to identify the one or more databases or use an all clause.

checkpoint [all | [dbname[, dbname[, dbname.....]]]
Ed Wagner
Ed Wagner
SSC Guru
SSC Guru (260K reputation)SSC Guru (260K reputation)SSC Guru (260K reputation)SSC Guru (260K reputation)SSC Guru (260K reputation)SSC Guru (260K reputation)SSC Guru (260K reputation)SSC Guru (260K reputation)

Group: General Forum Members
Points: 260111 Visits: 12225
gracechristopher06 (10/31/2016)
Fixing corrupted indexes

If the index on one of your system tables has been corrupted, you can use the sp_fixindex system procedure to repair the index. For syntax information, see the entry for sp_fixindex in “System Procedures” in the Adaptive Server Reference Manual.

StepsRepairing the system table index with sp_fixindex

Get the object_name, object_ID, and index_ID of the corrupted index. If you only have a page number and you need to find the object_name, see the Adaptive Server Troubleshooting and Error Messages Guide for instructions.

If the corrupted index is on a system table in the master database, put Adaptive Server in single-user mode. See the Adaptive Server Troubleshooting and Error Messages Guide for instructions.

If the corrupted index is on a system table in a user database, put the database in single-user mode and reconfigure to allow updates to system tables:

1> use master
2> go

1> sp_dboption database_name, "single user", true
2> go

1> sp_configure "allow updates", 1
2> go

Issue the sp_fixindex command:

1> use database_name
2> go1> checkpoint
2> go1> sp_fixindex database_name, object_name, index_ID
2> go

You can use the checkpoint to identify the one or more databases or use an all clause.

checkpoint [all | [dbname[, dbname[, dbname.....]]]

NoteYou must be assigned sa_role to run sp_fixindex.

Run dbcc checktable to verify that the corrupted index is now fixed.

Disallow updates to system tables:

1> use master
2> go1> sp_configure "allow updates", 0
2> go

Turn off single-user mode:

1> sp_dboption database_name, "single user", false
2> go1> use database_name
2> go1> checkpoint
2> go

You can use the checkpoint to identify the one or more databases or use an all clause, which means you do not have to issue the use database command.

checkpoint [all | [dbname[, dbname[, dbname.....]]]

StepsRepairing a nonclustered index on sysobjects

Perform steps 1 – 3, as described in “Repairing the system table index with sp_fixindex,” above.

Issue:

1> use database_name
2> go1> checkpoint
2> go1> select sysstat from sysobjects
2> where id = 1
3> go

You can use the checkpoint to identify the one or more databases or use an all clause.

checkpoint [all | [dbname[, dbname[, dbname.....]]]

Save the original sysstat value.

Change the sysstat column to the value required by sp_fixindex:

1> update sysobjects
2> set sysstat = sysstat | 4096
3> where id = 1
4> go

Run:

1> sp_fixindex database_name, sysobjects, 2
2> go

Restore the original sysstat value:

1> update sysobjects
2> set sysstat = sysstat_ORIGINAL
3> where id = object_ID
4> go

Run dbcc checktable to verify that the corrupted index is now fixed.

Disallow updates to system tables:

1> sp_configure "allow updates", 0
2> go

Turn off single-user mode:

1> sp_dboption database_name, "single user", false
2> go1> use database_name
2> go1> checkpoint
2> go

You can use the checkpoint to identify the one or more databases or use an all clause.

checkpoint [all | [dbname[, dbname[, dbname.....]]]



I'm not familiar with sp_fixindex. I was able to find a reference to it for SQL 2000, but nothing for SQL 2008.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Lincoln Burrows
Lincoln Burrows
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16436 Visits: 1993
here you can find the common database corruption reasons that might be occurred on your database
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search