How to Use DBCC CHECKDB Command for SQL Database Repair?

DBCC CHECKDB is used to check the physical and logical integrity of database objects, index relationships, and other structural checks. The failure of any of these checks will report consistency errors as part of the database console command.

The best method to repair errors in the database, reported by DBCC CHECKDB, is to run the last known good backup as recommended by Microsoft. However, if the backup is not available or is corrupted, you can try accessing the database in Emergency state.

The Emergency state allows accessing a database marked as suspect. It also allows running DBCC CHECKDB repair options to resolve database corruption. Once the database becomes accessible, repair it using the minimum level of repair option.

Note: Repair operations exclude any constraints applied to or between tables. So, if any of the table has one or more constraints, you must run DBCC CHECKCONSTRAINTS following a repair operation.

How to Use DBCC CHECKDB Command?

Before using DBCC CHECKDB, let’s look at its syntax.

Syntax:

DBCC CHECKDB    
    [ ( db_name | db_id | 0   
        [ , NOINDEX    
        | , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ]   
    ) ]   
    [ WITH    
        {   
            [ ALL_ERRORMSGS ]   
            [ , EXTENDED_LOGICAL_CHECKS ]    
            [ , NO_INFOMSGS ]   
            [ , TABLOCK ]   
            [ , ESTIMATEONLY ]   
            [ , { PHYSICAL_ONLY | DATA_PURITY } ]   
            [ , MAXDOP  = number_of_processors ]   
        }   
    ]   
]

There are a few options you can use to perform database consistency checks using DBCC CHECKDB. These options are as follows:

• database_name | database_id | 0: Specifies the name or ID of the database against which you need to run integrity checks. If the ‘database_name’ or ‘id’ is not specified and ‘0’ is specified, the current database will be used by default.
• NOINDEX: This argument performs only logical checks to reduce the total execution time. Also, it does not include non-clustered indexes in the checks.
• REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD: To repair a database, you need to run the DBCC CHECKDB command with any of these repair options:
  • REPAIR_ALLOW_DATA_LOSS: Use this repair option as a last resort to repair a SQL database, as it can lead to data loss. 
  • REPAIR_FAST: This repair option does not perform any repair actions. It helps maintain syntax for backward compatibility. 
  • REPAIR_REBUILD: The REPAIR_REBUILD option helps repair the database without any data loss. It can be used to repair missing rows in nonclustered indexes and for rebuilding an index.
• ALL_ERRORMSGS: This argument displays all the error messages for each object.
• EXTENDED_LOGICAL_CHECKS: Starting with SQL Server 2016, additional logical checks are not performed on filtered indexes and views. To perform the additional checks, you must use the EXTENDED_LOGICAL_CHECKS option.
• NO_INFOMSGS: DBCC output displays informational messages that are not related to the consistency errors. Running DBCC CHECKDB with NO_INFOMSGS can turn off the informational messages.
• TABLOCK: Uses locks rather than internal database snapshot to perform consistency checks on a database.
• ESTIMATEONLY: Specifies the estimated space required by the ‘tempdb’ database for executing the CHECKDB command.
• PHYSICAL_ONLY: It limits consistency checks on the physical structure of the database page, reducing run-time for DBCC CHECKDB on large databases.
• DATA_PURITY: It helps check a database for invalid or out-of-range column values.

Prerequisites to Run DBCC CHECKDB

You must ensure that your system meets the following prerequisites before you run DBCC CHECKDB to perform consistency checks on a database or repair it.

1. SQL Server Management Studio (SSMS) must be installed on your machine.
2. The user must have Administrator privileges.

Steps to Use DBCC CHECKDB for Repairing SQL Database

Open SSMS and follow these steps to repair SQL database by using DBCC CHECKDB:

Note: In below-mentioned steps, we will be using database_name as Dbtesting. Make sure to replace DBtesting with the name of your database.

Step 1: Set Database to Emergency Mode

Change the database status to EMERGENCY mode, which provides a read-only access to the administrator. To put the database in EMERGENCY mode, run the following query in SSMS:

ALTER DATABASE [Dbtesting] SET EMERGENCY

Step 2: Check for Corruption Errors

Once the administrator is able to access the database, execute the following DBCC CHECKDB command to analyze corruption errors in the database:

DBCC CHECKDB (Dbtesting) 

If DBCC CHECKDB detects any errors in the database, it will recommend appropriate repair options to fix the issue.

Step 3: Set SQL Server Database to SINGLE_USER Mode

Before using the DBCC CHECKDB repair options, put the corrupt database in SINGLE_USER mode to prevent other users from modifying the data during the repair process. To set SQL database mode to SINGLE_USER, follow these methods:

Method 1: Using Graphical User Interface (GUI)

Open SSMS and perform these steps to set the database to SINGLE_USER mode:

• Right-click the database you want to repair, and then click Properties.

• In the Database Properties window, click Options.

• Click the Restrict Access dropdown box under the State tab, select SINGLE_USER option, and then click OK.

Method 2: Using Transact-SQL (T-SQL) Commands

You can also set the database to SINGLE_USER mode, by running the following T-SQL query in SSMS:

ALTER DATABASE Dbtesting SET SINGLE_USER 

Step 4: Repair the Database

Once you have changed the database mode to SINGLE_USER, run DBCC CHECKDB with the REPAIR_ALLOW_DATA_LOSS repair option to repair SQL server database.

DBCC CHECKDB (N ’Dbtesting’, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS; 
GO

Note: While the REPAIR_ALLOW_DATA_LOSS repair option helps in repairing all reported errors in the SQL server database, it is not the best option for repairing database corruption. This repair option causes data loss. In fact, Microsoft recommends using the REPAIR_ALLOW_DATA_LOSS option as a last resort when you cannot restore a database from the backup. If you do not have a backup and cannot risk losing data, use a specialized MS SQL repair software to repair the database without any loss in database integrity.

Step 5: Set Database Back to MULTI_USER Mode

After successfully repairing the database, set the database to MULTI_USER mode by executing the following command:

ALTER DATABASE Dbtesting SET MULTI_USER

What To Do When DBCC CHECKDB Fails?

Running the DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS command might require deleting some database pages, resulting in loss of data. Also, DBCC CHECKDB commands might fail and return an error when dealing with severely corrupt SQL database files.

In such a case, use a specialized MS SQL repair software, such as Stellar Repair for MS SQL. The software repairs severely corrupt MS SQL database and restores all its components, while maintaining database integrity. The SQL recovery software helps reinstate access to the database with minimal manual efforts and time.

Key Features:

• Repairs both MDF and NDF database files
• Recovers all database components, including tables, keys, indexes, stored procedures, etc.
• Allows recovery of deleted records
• Recovers SQL tables with PAGE and ROW compression
• Supports selective recovery of database objects
• Previews recoverable database objects

To know the complete working process of the software, watch the video

Conclusion

A corrupt database could lead to unnecessary downtime and data loss. To overcome database-related errors, restore the database from the most recent backup. If current database backup does not exist or if the backup itself is corrupt, you can use DBCC CHECKDB ‘REPAIR_ALLOW_DATA_LOSS’ repair option to fix the database. But, this repair option involves risk of data loss. Also, DBCC CHECKDB might fail to fix the issue.

Use Stellar Repair for SQL software to repair MS SQL database and recover all its components with precision, maintaining database structure and integrity of database objects.