How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan
Certifications:
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Linux - 1 July 2024
- How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024
From time to time we have to check the integrity of our databases for any corruptions. In this article we will see the easiest and most efficient way to check for corruption in all instance databases. We will do this using the procedure DatabaseIntegrityCheck which has been made by Hallengren.
Of course this work is done by using T-SQL e.g. dbcc checkdb db_name ή maintenance plans which SQL Server has built-in but the above has some limitations.
There is an analysis of how we check for the integrity of a database using checkdb and how we fix any corruption in the article in the link here.
What more does it offer?
The procedure DatabaseIntegrityCheck can perform a check on all databases in the instance with a single command.
It also offers us greater control with the parameters it accepts during its execution.
The installation
Download from here the package with the procedures we will need:
or directly from the creator's site here.
After it is downloaded, we run the entire script on the base we want to call from it, for example on a custom base we have made for monitoring or on the master system base.
Then to start the process, we simply execute the procedure with the parameters as below:
EXECUTE dbo.DatabaseIntegrityCheck @Databases = 'ALL_DATABASES', @CheckCommands = 'CHECKDB', @MaxDOP = 0, @LogToTable = 'Y', @TabLock = 'N'
What exactly does each parameter define:
- @Databases we define whether the process will run on all databases, the systemic ones or the ones we have made.
- @CheckCommands we define what type of check it will do, we leave it at the default which is CHECKDB.
- @MaxDOP we define the maximum parallelism of the cpu cores allowed, with 0 we define that we want it to use as many as are available.
- @LogToTable we define whether indexOptimize commands will be recorded in the commandlog table created when we installed the script.
- @TabLock we define whether it will use it tempdb using a snapshot of each table in it or will do tablock on the board, locking it until the process is complete. As a result, however, it will not be accessible.
There are detailed instructions for each parameter on the creator's website here.
How do I automate the process?
We can add the execution of the procedure to a SQL Server Agent Job.
To do this we go to Objects Explorer, SQL Server Agent, Right click on Jobs, New Job…
In the window that appears, go to the tab Steps and we choose New.
There we choose the master database where we had installed the procedure and add the script of the procedure:
In the tab Schedules we can define the frequency that the task will run. It would be good to choose a time that does not have a heavy workload with access to the data in the instance:
To see in detail each action that the procedure has done and when it did it
We simply make a select in the commandlog table:
select * from master..commandlog;
Bonus
With the following script that I have made, with just one click, it installs it MaintenanceSolution by Ola Hallengren along with ready configured jobs for Backup ,Database Integrity and Index Optimize with optimal parameters and timing.
Backup jobs are created disabled as we may have another solution for backup with a 3rd party tool. It also checks for Availability Group so that they run only if it is the Preferred Backup Replica (for backup / database integrity) and if it is Primary Replica (for Index Optimize).
All we need to do is run the following script on each SQL Server Instance:
