In Part.11, I summarized what to do when 'DBCC CHECKDB' command detects some errors. In this article, I would like to summarize how to run the 'DBCC CHECKDB' with REPAIR option to resolve database inconsistencies.
How to resolve database inconsistencies using the 'DBCC CHECKDB' command
In general, when a database is found to have inconsistencies, we consider restoring the database from a normal backup that does not have inconsistencies. However, there may be situations where you want to resolve the inconsistency in the existing database due to the fact that there is no normal backup of the database where the inconsistency occurred.
In this time, I will try to discuss how to resolve database inconsistencies using the 'DBCC CHECKDB' command.
[Caution]
- Depending on the status of the database inconsistency, the 'DBCC CHECKDB' command may not be able to resolve the database inconsistency.
- Depending on the status of the database inconsistent database, some data may be lost.
- Before executing the 'DBCC CHECKDB' command with REPAIR option, take a full backup of the database where the inconsistency occurs, and if possible, restore it to the test or staging environment after that try to repair database inconsistency.
[Prerequisite]
There are two tables 'tab1' and 'tab2' in the database 'TESTDB', and the following data exists in this database.
# table 'tab1' data
# table 'tab2' data
1) Run the 'DBCC CHECKDB' on the database where the inconsistency is occurring in order to determine the status of the database inconsistency.
| dbcc checkdb('TESTDB') go |
2) Check the command results to determine the status of the database inconsistency.
[Command Results (sample)]
From the above results, we can see the following,
- 3 consistency errors in index ID 1 (clustered index) on table 'tab1'
- 4 consistency errors occurred in index ID 2 (non-clustered index) on table 'tab2'
- The minimum repair level for the consistency errors are 'repair_allow_data_loss', which means that data may be lost after repair is performed.
Note that for table 'tab1', the inconsistency occurs in index ID 1 (clustered index), so resolving the inconsistency will delete the data pages including inconsistency data, and the data in the deleted data pages will be lost.
For table 'tab2', the inconsistency occurs only in index ID 2 (non-clustered index), which means that even if the inconsistency is resolved, no data will be lost.
3) Change the database to 'Single User Mode'.
| alter database TESTDB set single_user with rollback immediate go |
4) Execute the 'DBCC CHECKDB', specifying the minimum repair level ('repair_allow_data_loss' in this case) that was confirmed in 2).
| dbcc checkdb(TESTDB, repair_allow_data_loss) go |
5) Check the results of 4) and confirm that the database inconsistency has been resolved.
You can see that the clustered index on table 'tab1' has been rebuilt and the consistency error has been resolved.
You can see that the non-clustered index on table 'tab2' has been rebuilt and the consistency error has been resolved.
Verify that the inconsistency is resolved in the database "TESTDB".
6) Execute the 'DBCC CHECKDB' command again to confirm that the inconsistency has been resolved.
| dbcc checkdb('TESTDB') go |
7) Change the database to 'Multi-User Mode'.
| alter database TESTDB set multi_user go |
[Supplement]
For table 'tab1', the inconsistency occurred in the clustered index, so you can see that table data on repaired database has been lost.
For table "tab2", the inconsistency occurred only in the non-clustered index, so you can see that no data has been lost after the inconsistency was resolved.
When you use the 'DBCC CHECKDB' command to resolve inconsistencies, check which table data will be lost by resolving the inconsistencies, whether inconsistencies have occurred in system objects, etc., and chose the recovery method depending on deadline for problem resolution.
- Resolve consistency errors by the 'DBCC CHECKDB' command with REPAIR option.
- Restore Database from backup
- Extract data from existing database and restore database from backup
If the deadline for database recovery is short, it is recommended to restore the database from a backup taken in a state where no inconsistency has occurred, since the consistency check command may not always be able to recover inconsistencies.
In addition, by taking a full backup of the database before the work to resolve the inconsistency, it is possible to restore the database to the state before the inconsistency was resolved, even if the work to resolve the inconsistency deteriorates to a state where the database cannot be accessed.
For this reason, it is recommended to take a full backup of the database even if the deadline for database recovery is short.
Summary
In this article, I have summarized how to actually run the 'DBCC CHECKDB' command and resolve inconsistencies in the database.
Depending on the type of index where the inconsistency is occurring, some data may be lost, and if inconsistencies have found in system objects, it is not guaranteed that the database will work properly even if the inconsistencies are resolved, so it is recommended to run the 'DBCC CHECKDB' command periodically so that you can determine when the database inconsistency occurred.
