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:
- 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.
- 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.
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.
If (and only if) SQL Server cannot repair the database without the loss of data then use:
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.
Reference: http://www.cryer.co.uk/brian/sqlserver/howto_repair_a_database.htm
