SQL Server Database Integrity Check Best Practices
As SQL database administrators, running consistency checks against a database is crucial to ensure that the database is free from corruption. However, how you should run DBCC CHECKDB to perform integrity checks is often overlooked, especially in an environment with several SQL Server instances and databases on each server. There are some best practices you need to follow to run database integrity checks optimally.
In this article, we will be discussing the SQL Server database integrity check best practices you should know.
SQL Server Database Integrity Check Best Practices Worth Considering
Here are some of the SQL Server database integrity check best practices you should be following, but not limited to:
1. Run DBCC CHECKDB with subset commands
Running DBCC CHECKDB command to perform database consistency checks can be a resource-intensive process and may take a long time to complete. Performing the integrity check on all the databases across multiple server instances may not finish on time. Meaning, the process may exceed the maintenance window limit. In such a case, you need to do a full run of DBCC CHECKDB using the following subset commands:
- CHECKALLOC – It checks the disk space allocation structures consistency for a database.
- CHECKTABLE – It checks the integrity of all the pages and structures that make up the table or indexed view.
- CHECKCATALOG – The database must be online to check catalog consistency within a SQL database.
Executing DBCC CHECKDB with these options will help you perform integrity checks in phases.
2. Offload logical consistency checks where possible
A full run of integrity checks on a large database can take a long time to complete than earlier versions. Such behavior occurs due to the following reasons:
- By default, DBCC CHECKDB spends the majority of the time performing logical consistency checks on the data. So, the more data you have, the more time it will take to produce results.
- Complexity of some of the underlying database structures.
- New integrity checks have been introduced with new features.
To work around this, focus your efforts on checking the integrity of a page’s physical structure using the PHYSICAL_ONLY option. This check doesn’t find any logical issues; however, it can identify common hardware failures, torn pages, and checksum failures that can corrupt a user’s data. Also, running DBCC CHECKDB using WITH PHYSICAL_ONLY check on the primary database can skip CPU-intensive checks.
3. Run DBCC CHECKDB with other execution options
Consider using other checks when running DBCC CHECKDB on large databases, such as:
- NO_INFOMSGS: Sometimes, running DBCC CHECKDB produces irrelevant output. You may see thousands of red lines with informational messages that
might not be of any use. Use the NO_INFOMSGS option to suppress informational messages.
DBCC CHECKDB(Database_Name) with NO_INFOMSGS; |
- DATA_PURITY: By default, DBCC CHECKDB performs column-value integrity checks. However, when upgrading databases, you will need to use the DATA_PURITY check for checking the data integrity in a table column. The syntax is as follows:
DBCC CHECKDB(Database_Name) with DATA_PURITY; |
- EXTENDED_LOGICAL_CHECKS**:** Run this check with DBCC CHECKDB when running missing critical databases. It performs logical checks on indexed views, spatial indexes, or XML indexes. Here’s the syntax:
DBCC CHECKDB(Database_Name) with EXTENDED_LOGICAL_CHECKS; |
Note: Executing “DBCC CHECKDB with EXTENDED_LOGICAL_CHECKS” on a table in SQL Server 2016 with a “filtered nonclustered index (NCI) over a clustered columnstore index (CCI)” may fail. It may also lead to SQL error 5297. This is a SQL bug. Refer to this link for more information.
- TABLOCK: Run CHECKDB using the TABLOCK option when the database snapshot creation is failing. This option uses locks rather than database snapshots.
4. Restore backup on another server and run DBCC CHECKDB
Restore backup of your production database on another system (or a test server). After restoring the database, perform a full CHECKDB on it. Doing so helps offload the burden of running database integrity checks from the production system. It also helps validate backups. However, there are a couple of downsides to this practice. If your production database is 1 TB or more, you’ll need the same amount of space on the separate system. Another drawback is that if consistency errors are found, you might not know whether the production database is corrupt or there’s an issue with the test server.
5. Determine whether you should run DBCC CHEKCDB before or after backup
As long as you are running database integrity checks regularly, it doesn’t really matter when you should run DBCC CHECKDB. But, knowing whether you should run backup before or after executing the CHECKDB command can determine if the backup is good or not.
Essentially, if performing consistency checks using DBCC CHECKDB fails, your subsequent backup might also fail. But, you would be less certain whether you have a good or bad backup if you decided to run backup before executing the CHECKDB command; there’s a possibility that corruption occurred between the times running backup and then CHECKDB.
Note: If you can’t do a full run of DBCC CHECKDB in a maintenance window, execute backups WITH CHECKSUM.
End Note
Following the best practices discussed in this post can help you run database integrity checks optimally. However, if you still find your database inconsistent, you may consider using third-party SQL recovery tool to regain access to your database without downtime. Stellar Repair for MS SQL is an efficient and reliable tool you can use to restore the database and its data without any inconsistency.