How to repair indexes in a MS SQL database

By Allen Stoner

It rarely happens, but occassionally a MS SQL Server will database will become corrupt. This is often caused by an index getting out of sync and can be repaired with a simple DBCC rebuild. The sample also shows how to put a database into single user mode and take it back out of single user mode.

--  Database must be in single user mode to repair indexes
exec sp_dboption 'dbName', 'single user', 'TRUE'

--  Repair the indexes in the database 'dbName' by rebuilding them, this if the option
--    that is to prevent data loss.  There are other options to do it faster but they
--    may cause data loss.
dbcc checkdb (dbName, REPAIR_REBUILD )

--  Let users back into the database by turnning off single user mode
exec sp_dboption 'dbName', 'single user', 'FALSE'

How to repair indexes in a MS SQL database  (929 Views)