Corrupted index - find out what caused it

• terry999

  SSCertifiable

  Points: 5036

  More actions

  May 2, 2016 at 1:41 pm

  #326090

  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

  SSC Guru

  Points: 61141

  More actions

  May 2, 2016 at 5:22 pm

  #1876131

  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.

  "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

  SSC Guru

  Points: 183706

  More actions

  May 3, 2016 at 7:09 am

  #1876217

  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

  SSC Guru

  Points: 61141

  More actions

  May 3, 2016 at 7:27 am

  #1876227

  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.

  "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

  Old Hand

  Points: 305

  More actions

  October 31, 2016 at 3:25 am

  #1909559

  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

  SSC Guru

  Points: 287026

  More actions

  October 31, 2016 at 5:58 am

  #1909582

  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

• June 1, 2017 at 4:05 am

  This was removed by the editor as SPAM