Corrupted index - find out what caused it

  • terry999

    SSCarpal Tunnel

    Points: 4721

    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: 61026

    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 ModenHow to Post Performance Problems -- Gail ShawNasty fast set-based string manipulation functions:For splitting strings try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL Server 2012+)To split strings based on patterns try PatternSplitCMNeed to clean or transform a string? try NGrams, PatExclude8K, PatReplace8K, DigitsOnlyEE, or Translate8KI 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: 183532

    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: 61026

    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 ModenHow to Post Performance Problems -- Gail ShawNasty fast set-based string manipulation functions:For splitting strings try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL Server 2012+)To split strings based on patterns try PatternSplitCMNeed to clean or transform a string? try NGrams, PatExclude8K, PatReplace8K, DigitsOnlyEE, or Translate8KI 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

    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: 286957

    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.

  • This was removed by the editor as SPAM

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply