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