Sql-server – How to repair database consistency errors

dbcc-checkdbmicrosoft-dynamicssql server

I have a Microsoft Dynamics AX SQL Server database. I just run DBCC CHECKDB in order to check the integrity. After a few minutes I got at the end of the results the following:

CHECKDB found 0 allocation errors and 4 consistency errors in database
'AXPROD'. repair_rebuild is the minimum repair level for the errors
found by DBCC CHECKDB (AXPROD). DBCC execution completed. If DBCC
printed error messages, contact your system administrator.

Since I got 4 consistency errors I would like to know if there is a way to fix this, or a way to get more detailed information about these errors.

Best Answer

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.