Summary: This blog discusses steps to perform SQL database repair by running DBCC CHECKDB with REPAIR_REBUILD command. It also outlines the possible reasons causing database corruption and ways to fix it before repairing the database. The blog explains how to run ‘DBCC CHECKDB REPAIR_REBUILD with an example’ via step-by-step instructions. Also, it suggests an SQL repair tool as an alternative to the DBCC CHECKDB repair options.
As database administrators, you must be running the DBCC CHECKDB command to check your SQL database’s physical and logical consistency. These checks may fail due to corruption in the database. If DBCC CHECKDB reports consistency errors, you must identify the root cause behind database corruption to find the best possible solution to fix the errors.
Following are the most common reasons leading to SQL Server database corruption:
If hardware or software issues are not detected, the best solution to fix corruption is to restore the database from the most recent backup. If there is no good backup, you can fix the errors by running the DBCC CHECKDB command with a repair option needed to fix specific errors. The following section will discuss how to fix the database corruption errors when ‘REPAIR_REBUILD’ is recommended as the minimum repair level to resolve all errors reported by DBCC CHECKDB.
The REPAIR_REBUILD command helps rebuild corrupt pages or repair missing rows in non-clustered indexes. You can use it to perform database repair “without the possibility of data loss.”
Before you run the ‘DBCC CHECKDB REPAIR_REBUILD’ command, make sure to put the database in a SINGLE_USER mode by executing the below command:
|ALTER DATABASE your_dbname SET SINGLE_USER|
This is important because repairing a SQL db and running a DBCC CHECKDB command are two separate operations. Also, a database is in MULTI-USER mode. And to fix it, you need to bring it in SINGLE_USER mode to avoid getting any errors.
Once the database is set to SINGLE_USER mode, run the following command to repair the database:
|DBCC CHECKDB(‘your_dbname’, REPAIR_REBUILD)|
Once the database gets repaired, set the database back to MULTI_USER mode:
|ALTER DATABASE your_dbname SET MULTI_USER|
Here’s an example that shows an error message recommending running “repair_rebuild as the minimum repair level” on the VLDB database.
Follow these steps to resolve the table error:
Step 1: Open SQL Server Management Studio (SSMS) and run this command:
|ALTER DATABASE VLDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;|
Step 2: Once the db is in SINGLE_USER mode, right-click on Databases, and click Refresh.
Step 3: Now expand Databases by clicking on the “+” icon. You can see the ‘VLDB’ is in SINGLE_USER mode.
Step 4: Now, run the following DBCC CHECKDB repair rebuild command:
If there are no errors, the query will execute successfully. If so, bring the db in MULTI_USER mode.
The REPAIR_REBUILD option can only fix minor corruption errors. So, it may fail to resolve all the errors reported by CHECKDB and result in an error recommending “REPAIR_ALLOW_DATA_LOSS as the minimum repair level” as you can see in the image below.
Run “DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS” command as a last resort to repair the database, as it involves data loss risk. A better alternative is to use a professional SQL repair tool.
Stellar Repair for MS SQL software can help fix all types of SQL database corruption errors. It repairs the MDF file of the database and recovers all the objects, thereby helping you to restore the database to its original form without data loss.
If you get consistency errors from running the DBCC CHECKDB command, using the REPAIR_REBUILD option can help fix the errors without any data loss. However, it may fail to resolve all the consistency errors. You may have to run DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS to fix database corruption, but remember, it may lead to data loss. Using Stellar Repair for MS SQL software can help you fix a severely corrupted database and restore it without any changes to its original form.
Charanjeet is a Technical Content Writer at Stellar®who specializes in writing about databases, e-mail recovery, and e-mail migration solutions. She loves researching and developing content that helps database administrators, organizations and novices to fix multiple problems related to MS SQL and MySQL databases and Microsoft Exchange.