Nisarg Upadhyay
Task execution history

Automate consistency checks of SQL database using Windows Task Scheduler

April 20, 2021 by

The SQL database integrity check is one of the most crucial and important tasks of the database administrator. The database integrity task checks the structural integrity and allocation of all database objects and indexes. The integrity checks can be performed by using the DBCC CheckDB command. The CheckDB command is used to identify the corruption in the database. The command performs the following operations on the database.

  1. Executes the DBCC CHECKTABLE on every table and view
  2. Executes the DBCC CHECKCATALOG on the databases
  3. Executes the DBCC CHECKALLOC on the databases
  4. Validates the service broker data
  5. Validates the content of indexed views

You can read DBCC CHECKDB (Transact-SQL) to learn more about the DBCC CheckDB command.

Usually, DBAs create custom scripts to identify the corruption in the databases. But, if you are working on multiple databases, it becomes tedious to review the logs generated by the DBCC CheckDB command. So I have created a SQL stored procedure that executes across all databases, and if it finds any consistency errors, it sends the details to the DBA team.

In my previous article, Automate SQL database backups using Windows Task Scheduler, I had explained how we could automate the process to generate a full and differential backup of SQL database restored in SQL server express edition. This article explains how we can automate the consistency check of SQL database created in SQL Server express edition. To demonstrate, I have downloaded a sample database from here. I have installed SQL Server Express edition and restored the database using the following command:

Once a database is restored, let’s run the DBCC CheckDB command to view the errors.

Consistency error of SQL database

As you can see in the above image, the database is corrupted, and the consistency check command has reported errors. To perform the consistency check, I have created a stored procedure that

  1. Run the consistency check on all SQL databases, and if it finds any consistency errors, it inserts those errors in a temp table
  2. Extract the message from the temp table and send the HTML formatted email to the DBA team

The code of the stored procedure is shown below:

As you know, the SQL Server Express edition does not support database mail, but you can configure the database mail using T-SQL queries. You can read this article, Database Mail configuration in the SQL Server Express edition to understand the process in detail.

The SQL Server Express edition does not support SQL Server agents, so; we use the Windows task schedular. Windows schedular can execute the batch file, so I have created a batch file that executes a stored procedure using the SQLCMD command. The code of the batch file is the following.

Specify the command in the text editor and save the file as *.bat file. Now, let us create a task using windows task schedular. Open Control Panel Open Administrator tools Open Task Scheduler. In the task schedular, Click on Create a basic task. On the first screen, specify the name of the task and description.

Create basic task screen

The database consistency check should be performed every week, so specify weekly in the Task Triggers screen.

Task trigger screen

The script should be executed every Sunday at 1:00 AM, so specify 01:00:00 in the Start textbox. The job should be executed one time every week so specify 1 in Recur every textbox. Select Sunday from the list of the days.

Weekly schedule configuration screen

We are running a batch script to check the database corruption, so choose the Start a program option on the Action screen.

Task Action screen

On the Start, a Program screen, specify the full path of the batch file. You can find the file by browsing through the directories. In our case, the batch file has been created in C:\DatabaseScripts, so I have provided the batch file’s full path. Ensure the user who has created the schedule must have read-write access on the directory in which the batch file has been created. Click on Next.

Start a program screen

On the Summary screen, you can review the details of created task. Click on Finish.

Summary screen

You can view the task in task schedular windows. Now, let us test the configured task. To do that, right-click on Check Database integrity and click on Run. Alternatively, click on the link named Run from the right-pan.

View task history

The consistency check process begins.

DBCC CheckDB process begins

The process was completed successfully. You can verify the details from the Task History tab of the task schedular.

Task execution history

We have restored a corrupted SQL database, so we have received an email with the database consistency errors. The email looks like the following image.

Consistency error email

Summary

This article explained how we can identify database corruption using a batch script that has been scheduled in the Windows task schedular. This article can be useful to the entry-level database administrators who want to automate the database maintenance tasks of SQL database created or restored in SQL server express edition. In my next article, we will learn how we can automate the index maintenance of SQL database created in SQL Server express edition using Windows task scheduler. Stay tuned!

Nisarg Upadhyay
Latest posts by Nisarg Upadhyay (see all)
DBAtools, Jobs, Maintenance

About 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

1,559 Views