“Recently, I ran DBCC CHECKDB command to check the consistency of SQL Database. After running that command, I noticed that the database was not showing online. To fix it, I started SQL Service but it also went in the suspect-mode. I am not able to understand why DBCC CHECKDB fails. Is there any way to resolve this issue?”
Basically, DBCC CHECKDB command checks the physical and logical integrity of all database objects with DBCC CHECKALLOC, DBCC CHECKTABLE, and DBCC CHECKCATALOG. This command validates the content of every indexed view in the database. Moreover, it also checks the link-level consistency between table metadata and file system directories and files etc. In simpler words, one can say that DBCC CHECKDB notifies the user when data comes in a corrupted state.
Why DBCC CHECKDB Fails?
There can be numerous reasons behind the failure of DBCC CHECKDB command. However, major reasons are:
The command DBCC CHECKDB fails when it is run against the master if an internal database snapshot cannot be created.
When there is major corruption in SQL database then also DBCC CHECKDB commands fail and return an error.
What If DBCC CHECKDB Job Failed?
#Method 1: Restore From Backup
1. Open MS SQL Server Management Studio and connect database
2. Select the database and then right-click Tasks > Restore > Database
3. Now, you will see Restore Database window. Select the From Device and click on the button to specify the file location
4. Select Backup media as file option and click on Add button to insert the backup file location
5. Finally, select the backup file you need to restore and click on the OK button
#Method 2: Fix DBCC CHECKDB Command Failure
When DBCC CHECKDB fails , you can follow the steps given below:
- You can create a database snapshot of the database for which you want to perform the checks.
- Now, run the DBCC CHECK DB command against the database snapshot
- Drop the database snapshot after the completion of the DBCC CHECK command
#Method 3: Resolve Corruption Issues by SQL Recovery
When DBCC CheckDb fails, it could be the possibility that the job failed due to major level of corruption issues in the SQL Server. The user has to eliminate the corruption issues as soon as possible. If corruption is not removed,then it can lead to data loss. To fix major as well as minor corruption in SQL Server database the user can opt for an automated solution that is SysTools SQL Database Recovery Tool. This utility allows the user to repair corrupt MDF file and export them into the SQL Server database in the healthy state. Moreover, the software provides Quick and Advanced Scanning mode to repair SQL database by eliminating minor as well as major corruption. To fix corruption in SQL Server database, the user has to follow the step by step process explained below:
1. Download and Launch SQL Recovery Tool on your system
2. Browse MDF file to the software and Select Advanced Scan option
3. The software preview the scanning report and all components of SQL database file
4. After previewing all database components, Click on Export to save data in SQL Server Database.
Tip to Prevent Failure of DBCC CHECKDB Command
- Easily Detect Corruption By Checking Regular Maintenance Plan
You can easily set up a regular maintenance to check for corruption. For this, you have to run DBCC CHECKDB to use a maintenance plan.
- First, you have to expand the Management Node in SQL Server Management Studio and then right-click on Maintenance Plans
- Select New Plan and from the Toolbox windows, drag the Check Database Integrity Task over to the Maintenance Plan tab
- After that, when you will double click on the Check Database Integrity Task box in the Maintenance Plan tab. Then you will be able to choose which database you want to have checked during the process
- Finally, scheduling it is left, which is just similar to scheduling an Agent Job
DBCC CHECKDB command plays a very crucial role in SQL Server as it notifies the user when the database becomes corrupt. Sometimes, the DBCC CHECKDB command fails which indicates that the database is highly corrupt. In such a situation, the user can follow the solution explained in the above section. Moreover, to resolve the corruption issues instantly the user can make use of an automated solution.