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.
Possible Reasons Behind SQL Database Corruption
Following are the most common reasons leading to SQL Server database corruption:
- Faulty Hardware: Check for defects in disk controllers, device drivers, or other hardware components. If the consistency errors are caused due to hardware problems, ensure that the configuration of the hardware device meets with the Microsoft SQL Server Database Engine Input/Output requirements. Or else, contact your hardware vendor or device manufacturer to resolve the underlying hardware issues.
- Bugs in the SQL Software: If you can’t find any issue with the underlying hardware, check for software bugs, as Microsoft is known to cause index corruption or data loss while running an online index rebuild in SQL Server 2012/2014.
- Virus or Malware Attack: The SQL database files can get corrupted due to a virus or malware attack, turning the files inaccessible.
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.
How to Fix Corrupt SQL Database Using ‘DBCC CHECKDB With REPAIR_REBUILD’?
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_USERThis 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_USERDBCC CHECKDB with REPAIR_REBUILD Example
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.
What If ‘DBCC CHECKDB REPAIR_REBUILD’ Fails?
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 the “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.
Conclusion
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.
