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:
