Cry How to...

Repair a database

To determine whether a database needs to be repaired run:

dbcc checkdb('DB-NAME') with no_infomsgs

replacing 'DB-NAME' with the name of the database.

If this completes without displaying any errors then the database does not need to be repaired.

If the errors that come back contain lines saying:

... Run DBCC UPDATEUSAGE

Then the database does not need to be repaired, simply run:

dbcc updateusage('DB-NAME') with no_infomsgs

If a database does need to be repaired then:

1. if you can identify why the database needs to be repaired. Look in the windows system and application event logs to see if any problems have been logged which might account for the problem. For example is the problem caused by a failing disk? Often you will not be able to identify the cause, but if you can then remember to address it.
2. it is suggested that instead of repairing the database it be restored from the last reliable backup.

To repair a database the database must first be placed into single user mode:

alter database DB-NAME set SINGLE_USER

if the database is the MASTER or MSDB then instead consult "How to set a single database into single user mode".

once the database is in single user mode it can be repaired. There are a number of repair options but the two typically used are "REPAIR_REBUILD" and "REPAIR_ALLOW_DATA_LOSS". I suggest in the first instance using:

dbcc checkdb('DB-NAME',REPAIR_REBUILD)

this will make any repairs that SQL Server can perform without the loss of data.

dbcc checkdb('DB-NAME',REPAIR_ALLOW_DATA_LOSS)

once the database has been repaired it should be switched out of single user mode and back into multi-user mode:

set database DB-NAME set MULTI_USER

These notes have been tested against SQL Server 2005 running under Windows 2008 Standard Server.