Complete Guide for SQL Database Integrity Check

About SQL Database Integrity Check

Performing integrity checks is part of the SQL Server database maintenance plan that helps ensure consistency of the database.

To be more precise, running integrity checks help maintain the effectiveness of production database or restored backup by detecting consistency errors (corruption) in the database.

How Often Should You Run Database Integrity Checks on SQL Server Database?

Performing integrity checks can be a time-consuming and resource-intensive process. Thus, it is important to understand how often you should run integrity checks. To decide this, let’s take two examples:

• Your SQL database has turned corrupt or damaged. You don't have backups. The only way to restore the database is to run the repair option, which involves data loss risk. In that case, running integrity checks regularly (daily or hourly) can detect database corruption early, minimizing data loss and downtime.
• You have a comprehensive database backup strategy in place, allowing you to restore a SQL database with no or minimal data loss and downtime. In that case, you may consider running integrity checks once a week.  

How to Check Database Integrity in SQL Server?

Following are the two methods to check SQL database integrity:

Method 1 – Use SQL Server Management Studio (SSMS)

• Open SSMS and connect to your SQL Server instance.
• From Databases, right-click on the database for which you want to perform integrity checks, and then click the New Query option.
• In the ‘New Query’ window, type DBCC CHECKDB, and then click on the Execute button to run the query.

If running the query report “0 allocation errors and 0 consistency errors”, it means that the database is healthy.  If it reports consistency errors, check the recommended minimum level of repair you will need to perform at the bottom of message summary (refer to the image below):

Figure 1 - Executing DBCC CHECKDB to Run Database Integrity Check

Method 2 – Use Check Database Integrity Task

Follow these steps to check db integrity using ‘Check Database Integrity Task’ in SQL Server Integration Services (SSIS):

• Open SQL Server Data Tools (SSDT).

• In the window that opens, click on File > New > Project.

• In ‘New Project’ window that appears, select Integration Services Project, and then click OK.

• Under SSIS Toolbox, under Other Tasks, double-click on Check Database Integrity Task option. The task helps in verifying logical and physical integrity of the database objects.

• Double-click on the Check Database Integrity Task box. This will open a ‘Check Database Integrity Task’ pop-up window. In this window, create a new connection by clicking the New button.

• In ‘Connection Properties’ dialog box that opens, enter connection name, the name of your SQL Server and choose Windows Authentication (recommended) method. Click OK.

• Click the drop-down arrow next to Database(s): textbox. Select a single or multiple databases for which you want to run integrity checks. Press OK.

• You can optionally click on the View T-SQL button to view the DBCC CHECKDBA statement to perform a scheduled integrity check on the selected database(s).

Note: Check the ‘Include indexes’ checkbox if you want to perform integrity checks on the database index pages as well.

• DBCC CHECKDB statement to run a scheduled integrity check.

• Double-click the Execute T-SQL Statement Task and add the tasks with a constraint.
• Click the ‘Start Debugging’ button to execute the ‘Check Database Integrity Task’ package to run all the tasks you’ve created.

The tasks will be displayed with green status if everything is OK.

But, sometimes, the integrity checks may fail and show errors. This will happen when the SQL database is corrupted.

What to do if SQL Database Integrity Check Fails?

If SQL database integrity check fails on a database, it is advisable to restore the SQL database from the latest backup. If you don’t have backups, you can perform the database repair process by running DBCC CHECKDB command with ‘REPAIR_ALLOW_DATA_LOSS’ option.

Read this: How to Repair SQL Database using DBCC CHECKDB Command?

The ‘REPAIR_ALLOW_DATA_LOSS’ option can fix database consistency errors. However, it only helps fix the physical structure of the database, not the internal data integrity. In other words, running the DBCC CHECKDB command with ‘REPAIR_ALLOW_DATA_LOSS’ option can result in data loss.

You can also fix SQL database consistency errors, by running the ‘REPAIR_REBUILD’ command. The command is usually used for correcting issue in non-clustered indexes. This option doesn’t result in data loss.

Alternative to Fix SQL Database Consistency Errors

If executing the DBCC CHECKDB command failed to preserve data integrity and consistency of your SQL Server database, you can use an alternative SQL Database Repair Tool, such as Stellar Repair for MS SQL to resolve the issue.

The software repairs severely corrupted SQL database MDF/NDF files while preserving data integrity. It can fix corruption in SQL databases on both Windows and Linux systems. The SQL repair tool can recover all database objects, including deleted table records of SQL version 2019, 2017, 2016, and earlier versions.

Conclusion

When running SQL Server check database integrity periodically to ensure that the database is healthy and is not corrupted. This article explains the steps you need to perform in SSIS to perform integrity checks on a SQL Server database. It also discusses using the DBCC CHECKDB command with repair options when your integrity check fails. But, executing DBCC CHECKDB with ‘REPAIR_ALLOW_DATA_LOSS’ command can result in data loss and cause inconsistency errors. In that case, a better alternative is to use Stellar Repair for MS SQL to fix the database consistency errors without the risk of data loss.