Repairing a nonclustered index

Running sp_fixindex to repair a nonclustered index on sysobjects requires several additional steps.

Repairing a nonclustered index on sysobjects

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

2. Issue the following Transact-SQL query:

   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 databasess 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 sp_fixindex:

   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 databasess or use an all clause.

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

---

Copyright © 2003. Sybase Inc. All rights reserved.

View this book as PDF