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