Why running DBCC CHECKDB?
DBCC CHECKDB checks the logical and physical integrity of all the objects in a database and provides information of any corruption.
As performing DBCC CHECKDB is a resource exhaustive task it is recommended to run it on a production server when there is as less traffic as possible, or even better, as one of the ways to speed up the DBCC CHECKDB process, is to transfer the work to a different server by automating a process and run CHECKDB after a database restore. As a backup process is a copy of a database and a restored database will be exactly the same as an online database therefore if there were any inconsistencies or issues they will be in the backup, and found in a restore. By using this approach both restores will be tested and backups verified without any impact on a production database.
Running DBCC CHECKDB after a restore can also be a good practice if a backup’s quality is unknown.
In this article we will describe how to automate a DBCC CHECKDB after a database restore by automating DBCC CHECKDB after a database restore by using a SQL Server Agent job, Maintenance Plans, and ApexSQL Backup and explain some of the DBCC CHECKDB options available in ApexSQL Backup.
How to automate DBCC CHECKDB after a database restore by using a SQL Server Agent job
To automate DBCC CHECKDB after a database restore by using SQL Server Agent:
- Select a New Job from the SQL Server Agent node ➜ Jobs
Create a first step and specify a database backup to restore by using the following script:
RESTORE DATABASE AdventureWorks2014CheckDB FROM DISK = 'E:\EncBackup\AdventureWorks2014_Full_20150612_1916.bak' WITH RECOVERY GO
Add a second step to perform DBCC CHECKDB on a restored database by adding the following T-SQL script:
DBCC CHECKDB (AdventureWorks2014CheckDB)
To use different DBCC CHECKDB options use the following T-SQL scripts:
To check column values and to check are there column values that are out of range:
DBCC CHECKDB (AdventureWorks2014CheckDB) WITH DATA_PURITY
To subdue unnecessary informational messages:
DBCC CHECKDB (AdventureWorks2014CheckDB) WITH NO_INFOMSGS
To check indexed views, spatial, and XML indexes:
DBCC CHECKDB (AdventureWorks2014CheckDB) WITH EXTENDED_LOGICAL_CHECKS
To check only the physical structure of a database:
DBCC CHECKDB (AdventureWorks2014CheckDB) WITH PHYSICAL_ONLY
To enforce table-shared locking:
DBCC CHECKDB (AdventureWorks2014CheckDB) WITH TABLOCK
These options can be combined except the WITH PHYSICAL_ONLY option that cannot be combined with some of the options, which will be explained later in this article.
- Schedule a job to a desired frequency of executing.
Although relatively easy to set up using a SQL Server Agent job for these operations requires writing T-SQL scripts and a knowledge of DBCC CHECKDB options syntax as well as a knowledge of each option functionality and possibilities of combining.
How to automate DBCC CHECKDB after a database restore by using Maintenance Plan
Maintenance Plans can also be used to perform this action. Although the Maintenance Plans toolbar doesn’t contain a restore task the T-SQL task can be used.
To automate DBCC CHECKDB after a database restore by using Maintenance Plans:
- From the Management node in the Object Explorer pane, under Maintenance Plans select the New Maintenance Plan
- In the New Maintenance Plan name a new plan
From the Maintenance Plan Tasks toolbar select Execute T-SQL Statement Task
Double slick the task and paste a T-SQL script:
Add Check Database Integrity Task from the Maintenance Plan Tasks toolbar:
Double click on a task and select a database to run the task against:
When a maintenance plan is set up schedule it as a SQL Server Agent job:
Downside of this approach besides being more complicated to set up is that currently Check Database Integrity Task offers only indexes check as an additional option and performs a full DBCC CHECKDB check.
More options will be added in a SQL Server 2016 version.
How to automate DBCC CHECKDB after a database restore by using ApexSQL Backup
ApexSQL Backup a SQL backup and restore manager, offers an option to automatically perform DBCC CHECKDB command during a restore and allow user to choose between options to include in a check.
ApexSQL Backup enables the user to automate the process in just a few clicks without the need to write a T-SQL script and additional commands for additional options explained below as it offers them out of the box.
ApexSQL Backup also automatically disables options that cannot be combined if one of them is selected thus preventing the user to experience errors, for example:
Msg 2532, Level 16, State 2, Line 8
One or more WITH options specified are not valid for this command.
To automate DBCC CHECKDB after a database restore by using ApexSQL Backup:
- Select the Restore option from the ApexSQL Backup menu
In the Restore wizard dialog select a server to connect to and enter a database name:
Specify the exact backup file to restore or use the Folder scan option:
To run the job on a regular basis, the schedule for the job needs to be set. Click on the Schedule radio button on the bottom of the form, and the Schedule wizard will open automatically.
Set the frequency for the job in the Schedule wizard, and click OK to save the schedule:
In Advanced tab of the Restore wizard select the Verify database after restore with database integrity check (DBCC CHECKDB) option, and select the desired additional options:
Choosing the options
The WITH PHYSICAL_ONLY DBCC CHECKDB option
Selecting the WITH PHYSICAL_ONLY option forces DBCC CHECKDB to skip the consistency checks that are costly for the CPU providing a low overhead check of the physical consistency allowing that DBCC CHECKDB process runs significantly faster. The PHYSICAL_ONLY option switches on the NO_INFOMSGS option.
This option turns off other options of DBCC CHECKDB (the DATA_PURITY option and any repair options) that could be useful for more thorough corruption inspection.
The WITH DATA_PURITY DBCC CHECKDB option
SQL Server 2005 introduced the DATA_PURITY option that is added to the DBCC CHECKDB command. This option checks for the column values and detects the issues if a column values are out of range or invalid. For databases created in versions 2005 and older the DATA_PYRITY doesn’t have to be specified as it is checked automatically. This doesn’t apply on databases that are upgraded from an older versions and in those cases this option should be specified when DBCC CHECKDB is run.
The WITH EXTENDED_LOGICAL_CHECKS DBCC CHECKDB option
This option is used to perform consistency checks on indexed views, spatial indexes, and XML indexes. To perform consistency check on these types of indexes this option has to be added as this check is not performed by default since SQL Server 2008.
When checking an indexed view consistency DBCC CHECKDB checks does the indexed view match the view definition and reports is some rows are missing or if there are additional rows. Consistency checks of the XML indexes and spatial indexes is similar to a consistency check of the indexed views with small differences.
Additional rows for all three types of indexes are indicated with as 8907 errors, and the missing rows are indicated with the 8908 errors.
These checks are very time and space expensive to run and they depend on the indexes definitions, because the more complex an index definition is the more time is required to generate a copy of an index in the tempdb database, and the more space it will consume in the tempdb database.
The WITH TABLOCK DBCC CHECKDB option
DBCC CHECKDB uses the schema lock meaning that while the data is read other transactions can’t modify that data but the locked data can be read. The shared lock is released after a process of reading the locked data if finished.
If the WITH TABLOCK option is selected it will reduce the locking level and force DBCC CHECKDB to use the table-shared locking.
The WITH NO_IFOMSGS DBCC CHECKDB option
When performing DBCC CHECKDB it provides the user with a substantial number information in the output. By selecting the NO_INFOMSGS option will reduce the amount of information to only errors that are found and allow the user to identify reported error more quickly and easily.
Optionally, Email notifications can be set for this job. To do so, navigate to Notification tab of the wizard. Check the boxes for one or more job status that should trigger the notification, and add any relevant Email recipients to the list. Complete the job configuration by clicking OK button at the bottom of the page.
If all settings are configured properly, the success message is displayed:
June 22, 2015