7

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.

Tom V
  • 15,752
  • 7
  • 66
  • 87
VAAA
  • 213
  • 1
  • 2
  • 4

2 Answers2

10

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.

Paul White
  • 94,921
  • 30
  • 437
  • 687
4

For your question I guess important point of restoring from known valid backup is missed so I would add that point as well.

Although REPAIR_REBUILD is suggested as minimum level of repair and might work in your case you must know that when you run repair_rebuild its not guaranteed to solve your problem and remove corruption. If that happens restoring from backup would be good option. You can use repair_rebuild within a transaction and see the changes if you are Ok with it commit it otherwise rollback. Below is what BOL Article says

Since DBCC CHECKDB with any of the REPAIR options are completely logged and recoverable, Microsoft always recommends a user use CHECKDB with any REPAIR options within a transaction (execute BEGIN TRANSACTION before running the command) so that the user can confirm he/she wants to accept the results of the operation. Then the user can execute COMMIT TRANSACTION to commit all work done by the repair operation. If the user does not want to accept the results of the operation, he/she can execute a ROLLBACK TRANSACTION to undo the effects of the repair operations.

To repair errors, we recommend restoring from a backup. Repair operations do not consider any of the constraints that may exist on or between tables. If the specified table is involved in one or more constraints, we recommend running DBCC CHECKCONSTRAINTS after a repair operation. If you must use REPAIR, run DBCC CHECKDB without a repair option to find the repair level to use. If you use the REPAIR_ALLOW_DATA_LOSS level, we recommend that you back up the database before you run DBCC CHECKDB with this option.

So if there is any such constraint you might as well consider idea of restoring from valid backup. Before that you must run restore verifyonly for backup you are trying to restore to check whether backup is consistent. You must also note that only a successful restore can guarantee backup is consistent in ALL formats

Lastly you must check SQL Server errorlogs and windows eventviewer as to find out reason why inconsistency came in at first place. A lot of time bad hardware is the cause.

Shanky
  • 19,148
  • 4
  • 37
  • 58