SQL Server Integrity Check

DatabaseIntegrityCheck is the SQL Server Maintenance Solution’s stored procedure for checking the integrity of databases. DatabaseIntegrityCheck is supported on SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017, SQL Server 2019, SQL Server 2022, Azure SQL Database, and Azure SQL Managed Instance.

Download

Download MaintenanceSolution.sql. This script creates all the objects and jobs that you need. You can also download the objects as separate scripts. The SQL Server Maintenance Solution is available on GitHub.

License

The SQL Server Maintenance Solution is free.

Parameters

Databases

Select databases. The keywords SYSTEM_DATABASES, USER_DATABASES, ALL_DATABASES, and AVAILABILITY_GROUP_DATABASES are supported. The hyphen character (-) is used to exclude databases, and the percent character (%) is used for wildcard selection. All of these operations can be combined by using the comma (,).

Value	Description
SYSTEM_DATABASES	All system databases (master, msdb, and model)
USER_DATABASES	All user databases
ALL_DATABASES	All databases
AVAILABILITY_GROUP_DATABASES	All databases in availability groups
USER_DATABASES, -AVAILABILITY_GROUP_DATABASES	All user databases that are not in availability groups
Db1	The database Db1
Db1, Db2	The databases Db1 and Db2
USER_DATABASES, -Db1	All user databases, except Db1
%Db%	All databases that have “Db” in the name
%Db%, -Db1	All databases that have “Db” in the name, except Db1
ALL_DATABASES, -%Db%	All databases that do not have “Db” in the name

CheckCommands

Specify the integrity check commands to be performed.

Value	Description
CHECKDB	Check the database. This is the default.
CHECKFILEGROUP	Check the filegroups.
CHECKTABLE	Check the tables and the indexed views.
CHECKALLOC	Check the disk space allocation structures.
CHECKCATALOG	Check the catalog consistency.
CHECKALLOC,CHECKCATALOG	Check the disk space allocation structures and the catalog consistency.
CHECKFILEGROUP,CHECKCATALOG	Check the filegroups and the catalog consistency.
CHECKALLOC,CHECKTABLE,CHECKCATALOG	Check the disk space allocation structures, the tables and the indexed views, and the catalog consistency.

DatabaseIntegrityCheck uses these SQL Server DBCC commands: DBCC CHECKDB to check the database, DBCC CHECKFILEGROUP to check the filegroups, DBCC CHECKTABLE to check the tables and the indexed views, DBCC CHECKALLOC to check the disk space allocation structures, and DBCC CHECKCATALOG to check the catalog consistency.

PhysicalOnly

Limit the checks to the physical structures of the database.

Value	Description
Y	Limit the checks to the physical structures of the database.
N	Do not limit the checks to the physical structures of the database. This is the default.

The PhysicalOnly option in DatabaseIntegrityCheck uses the PHYSICAL_ONLY option in the SQL Server DBCC CHECKDB, DBCC CHECKFILEGROUP, and DBCC CHECKTABLE commands.

DataPurity

Check for column values that are not valid or out-of-range.

Value	Description
Y	Check for column values that are not valid or out-of-range.
N	No not check for column values that are not valid or out-of-range. This is the default.

The DataPurity option in DatabaseIntegrityCheck uses the DATA_PURITY option in the SQL Server DBCC CHECKDB and DBCC CHECKTABLE commands.

NoIndex

Do not check nonclustered indexes.

Value	Description
Y	Do not check nonclustered indexes.
N	Check nonclustered indexes. This is the default.

The NoIndex option in DatabaseIntegrityCheck uses the NOINDEX option in the SQL Server DBCC CHECKDB, DBCC CHECKFILEGROUP, DBCC CHECKTABLE, and DBCC CHECKALLOC commands.

ExtendedLogicalChecks

Perform extended logical checks.

Value	Description
Y	Perform extended logical checks.
N	Do not perform extended logical checks. This is the default.

The ExtendedLogicalChecks option in DatabaseIntegrityCheck uses the EXTENDED_LOGICAL_CHECKS option in the SQL Server DBCC CHECKDB command.

You cannot combine the options PhysicalOnly and ExtendedLogicalChecks.

TabLock

Use locks instead of an internal database snapshot.

Value	Description
Y	Use locks to perform the consistency checks.
N	Use an internal database snapshot to perform the consistency checks. This is the default.

The TabLock option in DatabaseIntegrityCheck uses the TABLOCK option in the SQL Server DBCC CHECKDB, DBCC CHECKFILEGROUP, DBCC CHECKTABLE, and DBCC CHECKALLOC commands.

FileGroups

Select filegroups. The ALL_FILEGROUPS keyword is supported. The hyphen character (-) is used to exclude filegroups, and the percent character (%) is used for wildcard selection. All these operations can be combined by using the comma (,).

Value	Description
ALL_FILEGROUPS	All filegroups
Db1.FileGroup1	The filegroup FileGroup1 in the database Db1
Db1.FileGroup1, Db2.FileGroup2	The filegroup FileGroup1 in the database Db1 and the filegroup FileGroup2 in the database Db2
ALL_FILEGROUPS, -Db1.FileGroup1	All filegroups except the filegroup FileGroup1 in the database Db1
Db1.%FileGroup%	All filegroups in the database Db1 that have “FileGroup” in the name

This option can be used only if CHECKFILEGROUPS is specified in the CheckCommands option.

Objects

Select objects. The ALL_OBJECTS keyword is supported. The hyphen character (-) is used to exclude objects, and the percent character (%) is used for wildcard selection. All these operations can be combined by using the comma (,).

Value	Description
ALL_OBJECTS	All objects
Db1.Schema1.Tbl1	The object Schema1.Tbl1 in the database Db1
Db1.Schema1.Object1, Db2.Schema2.Object2	The object Schema1.Tbl1 in the database Db1 and the object Schema2.Tbl2 in the database Db2
ALL_OBJECTS, -Db1.Schema1.Object1	All objects except the object Schema1.Object1 in the database Db1
Db1.Schema1.%	All objects in the schema Schema1 in the database Db1

This option can be used only if CHECKTABLE is specified in the CheckCommands option.

MaxDOP

Specify the number of CPUs to use when checking the database, filegroup or table. If this number is not specified, the global maximum degree of parallelism is used.

The MaxDOP option in DatabaseIntegrityCheck uses the MAXDOP option in the SQL Server DBCC CHECKDB, DBCC CHECKFILEGROUP, and DBCC CHECKTABLE commands.

AvailabilityGroups

Select availability groups. The keyword ALL_AVAILABILITY_GROUPS is supported. The hyphen character (-) is used to exclude availability groups, and the percent character (%) is used for wildcard selection. All of these operations can be combined by using the comma (,).

Value	Description
ALL_AVAILABILITY_GROUPS	All availability groups
AG1	The availability group AG1
AG1, AG2	The availability groups AG1 and AG1
ALL_AVAILABILITY_GROUPS, -AG1	All availability groups, except AG1
%AG%	All availability groups that have “AG” in the name
%AG%, -AG1	All availability groups that have “AG” in the name, except AG1
ALL_AVAILABILITY_GROUPS, -%AG%	All availability groups that do not have “AG” in the name

AvailabilityGroupReplicas

Specify which replicas in availability groups that should be checked.

Value	Description
ALL	Perform the checks on all replicas. This is the default.
PRIMARY	Perform the checks on the primary replica.
SECONDARY	Perform the checks on the secondary replicas.
PREFERRED_BACKUP_REPLICA	Perform the checks on the preferred backup replica.

Updateability

Select READ_ONLY/READ_WRITE - databases.

Value	Description
ALL	READ_ONLY and READ_WRITE - databases. This is the default.
READ_ONLY	READ_ONLY - databases
READ_WRITE	READ_WRITE - databases

is_read_only in sys.databases is used to check if a database is READ_ONLY or READ_WRITE.

TimeLimit

Set the time, in seconds, after which no commands are executed. By default, the time is not limited.

LockTimeout

Set the time, in seconds, that a command waits for a lock to be released. By default, the time is not limited.

The LockTimeout option in IndexOptimize uses the SET LOCK_TIMEOUT set statement in SQL Server.

LockMessageSeverity

Set the severity for lock timeouts and deadlocks.

Value	Description
10	This is an informational message.
16	This is an error message. This is the default.

StringDelimiter

Specify the string delimiter. By default, the string delimiter is comma.

DatabaseOrder

Specify the database order.

Value	Description
NULL	The order that the databases have been specified in. Then ascending by the database name. This is the default.
DATABASE_NAME_ASC	Ascending by the database name
DATABASE_NAME_DESC	Descending by the database name
DATABASE_SIZE_ASC	Ascending by the database size
DATABASE_SIZE_DESC	Descending by the database size
DATABASE_LAST_GOOD_CHECK_ASC	Ascending by LastGoodCheckDbTime in DATABASEPROPERTYEX
DATABASE_LAST_GOOD_CHECK_DESC	Descending by LastGoodCheckDbTime in DATABASEPROPERTYEX
REPLICA_LAST_GOOD_CHECK_ASC	Ascending by the last successful checkdb in the dbo.CommandLog - table
REPLICA_LAST_GOOD_CHECK_DESC	Descending by the last successful checkdb in the dbo.CommandLog - table

DatabasesInParallel

Process databases in parallel.

Value	Description
Y	Process databases in parallel.
N	Process databases one at a time. This is the default.

You can process databases in parallel by creating multiple jobs with the same parameters, and add the parameter @DatabasesInParallel = 'Y'.

LogToTable

Log commands to the table dbo.CommandLog.

Value	Description
Y	Log commands to the table.
N	Do not log commands to the table. This is the default.

Execute

Execute commands. By default, the commands are executed normally. If this parameter is set to N, then the commands are printed only.

Value	Description
Y	Execute commands. This is the default.
N	Only print commands.

Examples

A. Check the integrity of all user databases

EXECUTE dbo.DatabaseIntegrityCheck
@Databases = 'USER_DATABASES',
@CheckCommands = 'CHECKDB'

B. Check the physical integrity of all user databases

EXECUTE dbo.DatabaseIntegrityCheck
@Databases = 'USER_DATABASES',
@CheckCommands = 'CHECKDB',
@PhysicalOnly = 'Y'

C. Check the integrity of all user databases, using the option not to check nonclustered indexes

EXECUTE dbo.DatabaseIntegrityCheck
@Databases = 'USER_DATABASES',
@CheckCommands = 'CHECKDB',
@NoIndex = 'Y'

D. Check the integrity of all user databases, using the option to perform extended logical checks

EXECUTE dbo.DatabaseIntegrityCheck
@Databases = 'USER_DATABASES',
@CheckCommands = 'CHECKDB',
@ExtendedLogicalChecks = 'Y'

E. Check the integrity of the filegroup PRIMARY in the database AdventureWorks

EXECUTE dbo.DatabaseIntegrityCheck
@Databases = 'AdventureWorks',
@CheckCommands = 'CHECKFILEGROUP',
@FileGroups = 'AdventureWorks.PRIMARY'

F. Check the integrity of all filegroups except the filegroup PRIMARY in the database AdventureWorks

EXECUTE dbo.DatabaseIntegrityCheck
@Databases = 'USER_DATABASES',
@CheckCommands = 'CHECKFILEGROUP',
@FileGroups = 'ALL_FILEGROUPS, -AdventureWorks.PRIMARY'

G. Check the integrity of the table Production.Product in the database AdventureWorks

EXECUTE dbo.DatabaseIntegrityCheck
@Databases = 'AdventureWorks',
@CheckCommands = 'CHECKTABLE',
@Objects = 'AdventureWorks.Production.Product'

H. Check the integrity of all tables except the table Production.Product in the database AdventureWorks

EXECUTE dbo.DatabaseIntegrityCheck
@Databases = 'USER_DATABASES',
@CheckCommands = 'CHECKTABLE',
@Objects = 'ALL_OBJECTS, -AdventureWorks.Production.Product'

I. Check the disk-space allocation structures of all user databases

EXECUTE dbo.DatabaseIntegrityCheck
@Databases = 'USER_DATABASES',
@CheckCommands = 'CHECKALLOC'

J. Check the catalog consistency of all user databases

EXECUTE dbo.DatabaseIntegrityCheck
@Databases = 'USER_DATABASES',
@CheckCommands = 'CHECKCATALOG'

Execution

You can execute the stored procedures from T-SQL job steps, or from CmdExec job steps with sqlcmd and the -b option.

SQL Server version	Job type
SQL Server 2008 and 2008 R2 on Windows	CmdExec job steps with sqlcmd and the -b option
SQL Server 2012, 2014, 2016, and 2017 on Windows	T-SQL job steps or CmdExec job steps with sqlcmd and the -b option
SQL Server 2017 on Linux	T-SQL job steps
Azure SQL Database Managed Instance	T-SQL job steps

There is a problem in SQL Server 2005, 2008 and 2008 R2 that a T-SQL job step stops executing after the first error. Use CmdExec job steps with sqlcmd and the -b option on these versions.

You can use the MaintenanceSolution.sql script to create the jobs. It will create CmdExec job steps with sqlcmd on SQL Server 2005, 2008 and 2008 R2, and T-SQL job steps on later versions.