In this article, I am going to explain how we can create a maintenance plan to automate the SQL Server DBCC CheckDB command.

The database consistency check validates the structural integrity and the allocation of the user table data pages and indexes pages. If the SQL Server database has corruption or has any integrity problem, the DBCC CheckDB command provides the details, and database administrators can handle it to fix the integrity issues.

Let me show you the step-by-step process to create a maintenance plan to perform a database consistency check. To create a maintenance plan, Launch SQL Server Management studio (SSMS) 🡪 Right-click on Management 🡪 Select New Maintenance Plan…

Specify the appropriate name of the maintenance plan.

As shown in the following image, drag the Check Database Integrity Task from the maintenance plan toolbox and drop it on the Maintenance plan designer window.

To configure the SQL Server DBCC CheckDB, double click on Check Database Integrity Task. A dialog box opens. In the dialog box, we can configure the following parameters.

1. Include index: This option allows us to perform an integrity check of the index pages and table data page
2. Physical Only: This option allows us to check the physical structure of the record header, page, and allocation consistency of the database. When you are running the consistency check on the large production database, this option reduces the time to complete the consistency check process
3. TabLock: When you are running the consistency check on the production databases under heavy load, this option makes the consistency check faster. When you enable this option, the SQL Server does not obtain the locks; instead, it uses the database internal snapshot, and it obtains an exclusive lock for a shorter time
4. Max Degree of parallelism: If you want to run the consistency check using multiple threads, you can specify the number of the threads in the text box next to the Max Degree of parallelism textbox

Now, let us understand the configuration process.

Configure the connection

In our case, we have not configured the connection yet, so to configure it, click on New.

In the Connection Properties dialog box, Specify the Connection Name in the Connection name textbox and specify the hostname in Specify the following to connect to SQL Server data text box. In our case, I have given the hostname of my workstation.

Click OK to create the connection and close the dialog box. Once the connection is configured, select the databases on which you want to perform the consistency check. To do that, click on Databases drop-down box.

We want to run the consistency check on all user databases, so choose All User Databases. Also, we want to exclude the databases that are not ONLINE, so I have checked Ignore databases where the state is not an online option and click OK.

Now, as per requirement, I have enabled the following options:

1. I want to check the consistency of the tables and indexes, so; I have selected Include Index Option
2. I want to check the integrity of the physical structure of the database only, so I have selected the Physical Only option
3. I do not want to put the lock on the tables on which the consistency check is running, so I have not selected the TabLock option
4. The consistency check should run using all available threads, so I have not changed the Max Degree ofParallelism value

Following is the Screenshot of configured options.

Click OK to save the configuration of the Check database integrity task and close the dialog box. The consistency check maintenance plan looks like the following image:

Configure the schedule

We want to run the job to be executed at 2 AM every Sunday, so we must schedule the job accordingly. To configure the job schedule, click on the calendar icon in maintenance plan designer.

In the New Job Schedule dialog box, set the values of parameters as shown below.

1. Schedule Type: Recurring
2. Occurs: Weekly
3. Recurs every: 1 week on Sunday
4. Occurs once at 02:00:00

Screenshot

After configuring the schedule, the maintenance plan looks like the following image:

Once a plan is created, click on the Save button from the menu bar to save the plan.

Test the maintenance plan

You can view the list of maintenance plans created on the SQL Server under the management node. To run the maintenance plan, Expand Management 🡪 Expand Maintenance Plans 🡪 Click on Execute.

Once the maintenance plan completes successfully, you will see the following dialog box.

Summary

This article explains how we can configure the database maintenance plan to automate the SQL Server DBCC CheckDB process. This article is useful to the entry-level DBAs working on a smaller database infrastructure and who want to automate the database maintenance plans.

• Author
• Recent Posts

Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration.

He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

Latest posts by Nisarg Upadhyay (see all)

• Different ways to identify and change compatibility levels in SQL Server - July 22, 2024
• Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
• Changing the location of FILESTREAM data files in SQL Database - October 14, 2022