Skip to Content

How to Repair the System Table Index ?

Please follow the below steps to repair the Index :

1. Get the object name, object ID, and index ID of the corrupted index.

2.  If the corrupted index is on a system table in the master database, put the Server in single-user mode using -m option as below :

startserver -m -f runserver_file

3. Connect to server using isql with a user having sa_role.

4. If the corrupted index is on a system table in a user database, put the database in single-user mode :

1> use master

2> go

1> sp_dboption database_name, "single user", true

2> go

1> use database_name

2> go

1> checkpoint

2> go

5. Set the configuration parameter to allow updates to system tables:

1> use master

2> go

1> sp_configure "allow updates", 1

2> go

6. Run the sp_fixindex command as below :

1> use database_name

2> go

1> sp_fixindex database_name, object_name, index_ID

2> go


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

1> dbcc checktable(object_name)

2> go


8. Disallow updates to system tables:

1> use master

2> go

1> sp_configure "allow updates", 0

2> go


9. Put the database in Multiuser Mode :

1> sp_dboption database_name, "single user", false

2> go

1> use database_name

2> go

1> checkpoint

2> go

10. If server started in single user mode, Please shutdown and then start the server in Normal mode (without using -m option).




Tags:
Former Member