How to Use DBCC CHECKDB to Check the Integrity Check in SQL DB

Database integrity refers to the accuracy, consistency, and reliability of the data stored within a database. It ensures that data remains uncorrupted, and adheres to defined rules. In SQL databases, integrity is supported through constraints like primary keys, foreign keys, unique constraints, and check constraints, alongside physical and logical data structure maintenance.

Designed as a built-in utility for Microsoft SQL Server, DBCC CHECKDB offers database administrators (DBAs) a powerful way to detect and address potential integrity issues before they escalate. In this article, we will see how to perform SQL database integrity checks using this tool.

1. How to Perform SQL Database Integrity Checks?

We can perform SQL database integrity checks by using the DBCC CHECKDB command in SQL Server. This command checks the physical and logical integrity of all the database objects. To check the database integrity using the DBCC CHECKDB command, follow the steps below:

• Open SQL Server Management Studio (SSMS) and connect to your SQL Server instance.
• From Databases, right-click on the database on which you want to perform integrity checks, and then click the New Query option.
• In the ‘New Query’ window, run the below command:

DBCC CHECKDB database15;

If the command reports “0 allocation errors and 0 consistency errors”, it indicates that the database is healthy. However, if it returns any integrity and inconsistency errors, it means there is corruption in the database.

If you need to check the integrity of a single table in the database, then run the below command:

DBCC CHECKTABLE (‘DB.12’);
Go

You can also schedule the database integrity checks. For this, you can use the Maintenance Plans feature in the SQL Server Management Studio (SSMS). Here are the steps:

• In the Object Explorer, click on Management.

• Right-click on Management Plans and select New Maintenance Plan.

• Provide a name to the maintenance plan and click OK.

• From the Maintenance Plan toolbox, drag the “Check database Integrity” task to the design window.

• Double-click on the “Check Database Integrity” task.
• In the Check Database Integrity Task window, select the database on which you want to schedule the database integrity task. You can also select other options, like Include indexes, etc. Click OK.

• Next, click on New job schedule plan, specify the frequency (daily, weekly etc.), and then click OK.

2. What If the DBCC CHECKDB Command shows Consistency Errors or Corruption in the Database?

If the DBCC CHECKDB command shows consistency issues or corruption in the database, then the first option is to restore the database from backup. However, if the backup file is obsolete or not available, then you can repair the database using the DBCC CHECKDB commands. Follow the below steps:

Note: Make sure you have administrative privileges on the database you are repairing.

• First, set your database to SINGLE_USER mode. Here’s the command to do so:

ALTER DATABASE Database15 SET SINGLE_USER 

• Next, run the below command to repair the database:

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

• Once your database is repaired, then set the database to MULTI_USER mode by running the below command:

ALTER DATABASE Database15 SET MULTI_USER

The DBCC CHECKDB command can help you resolve inconsistency errors in SQL database. But using this command with the REPAIR_ALLOWS_DATA_LOSS option can result in the data loss.

3. Alternative Solution to Repair Corrupt Database without Data Loss

If the DBCC CHECKDB command fails to resolve the reported errors, you can use an advanced SQL repair tool, like Stellar Repair for MS SQL. It can repair the corrupt SQL database file with complete precision and integrity. It can save the repaired objects to a new SQL database file, thus helping resolve any inconsistency and integrity issues in the database.

It can also save the repaired database objects in other file formats, like CSV, HTML, and XLS. It can even recover deleted records from the database file. The tool is easy to use and no technical expertise is required to use this tool.

4. Conclusion

Above, we have discussed how to perform an integrity check on MS SQL Server database. If the integrity check fails, it indicates consistency issues in the database. To fix the issues and repair the database within minimal downtime, you can use specialized SQL database recovery tools, like Stellar Repair for MS SQL. It can assist you in recovering the data from the damaged database file as quickly as possible and with no data loss.

The tool can repair both MDF and NDF files. With this tool, you can open the SQL database in any state and of any size. It can help in resolving corruption-related errors, like 824, 2512, 2511, 8994, 823, 605, 7987, and others. The tool supports SQL Server 2022, 2019, 2017, and earlier versions.

Happy Learning !!