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. See Adaptive Server Reference Manual: Procedures.

StepsRepairing the system table index with sp_fixindex

  1. 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.

  2. 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.

  3. 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
    
  4. 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.

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

  6. Disallow updates to system tables:

    1> use master
    2> go1> sp_configure "allow updates", 0
    2> go
    
  7. 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

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

  2. 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.....]]]
    
  3. Save the original sysstat value.

  4. 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
    
  5. Run:

    1> sp_fixindex database_name, sysobjects, 2
    2> go
    
  6. Restore the original sysstat value:

    1> update sysobjects
    2> set sysstat = sysstat_ORIGINAL
    3> where id = object_ID
    4> go
    
  7. Run dbcc checktable to verify that the corrupted index is now fixed.

  8. Disallow updates to system tables:

    1> sp_configure "allow updates", 0
    2> go
    
  9. 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.....]]]