To determine whether a database needs to be repaired,
Run this commanddbcc checkdb('DB-NAME') with no_infomsgs
'DB-NAME'- Name of your database
If this shows no errors then the database doesnt need to be repaired.
But if you get and errors aying: "Run DBCC UPDATEUSAGE"Then the database is corrupted.
Run the CMD Command as:
dbcc updateusage('DB-NAME') with no_infomsgs
If your database is severely damaged then you need to identify the reason behind the corruption. Check the event logs to see if any problems have been logged. For example is the problem caused by a failing disk?
In some cases, we are not able to identify the cause behind the problem, and that's why we are suggested to take time to time backup of our important data or records.
To perform repair, the database must be placed into single user mode:
alter database DB-NAME set SINGLE_USERif 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 types 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_USERThese notes have been tested against SQL Server 2005 running under Windows 2008 Standard Server.
When manual methods fail to repair SQL Server Database then use Sql Database recovery