I would like to know if there is a way to fix this
These consistency errors may be fixable with the REPAIR_REBUILD
option of DBCC CHECKDB
:
Performs repairs that have no possibility of data loss. This can
include quick repairs, such as repairing missing rows in non-clustered
indexes, and more time-consuming repairs, such as rebuilding an index.
As Shanky's answer mentions, any DBCC
repair should also be performed inside a transaction, so you can inspect the changes before committing to them.
As always, please ensure you have a completely recoverable set of backups (including the log tail if applicable) before running the rebuild. If you have a complete set of valid backups (including the log tail as applicable) and you can afford the downtime, restoring might be the preferred option. Be sure not to overwrite the current database if you do this, just in case the restore fails, or it is not as complete as you expected. Of course, it's quite likely the restored database would contain the corruption again, depending on how and when it occurred :)
or a way to get a more detailed information about this errors
Details of the four consistency errors are in the DBCC CHECKDB
output, before the summary section at the end. You should review these to ensure you understand the problem, and what may have caused it, before attempting any repair.
You can reduce the amount of DBCC CHECKDB
output using the WITH NO_INFOMSGS
option.
Add the DBCC
error message details to your question if you need help analyzing the errors. It is important to identify and correct any underlying hardware problem that might have caused the corruption.
Depending on the details of the corruption, there may be other ways to fix the problems (such as manually rebuilding a nonclustered index).
If the repair or rebuild is successful, you will need to check the database again with DBCC CHECKDB
with the fullest set of checks supported by your version of SQL Server.
Running this on a sufficiently corrupted database results in the following error message:
Msg 7909, Level 20, State 1, Line 1
The emergency-mode repair failed.You must restore from backup.
This leads me to estimate that REPAIR
is simply the short form of REPAIR_ALLOW_DATA_LOSS
. In the very least, I would treat it as the same when it comes to expectations around recoverability of the database in question. In other words, this should be considered the operation of last resort, only to be tried when all else fails. Make sure you make a physical copy of the database before running this process.
Best Answer
Handy corruption demo script has been created by Paul Randal. These are really good and would help you a lot. Please browse below links
Corruption demo databases and scripts
Using DAC connection to fix corrupt system tables
Various SQL Server Database corruption scenarios