SQL Server Maintenance Solution

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 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, and SQL Server 2014.

Download

Download MaintenanceSolution.sql. This script creates all the objects and jobs that you need. You can also download the objects as separate scripts.

License

The SQL Server Maintenance Solution is free.

Parameters

Databases

Select databases. The keywords SYSTEM_DATABASES, USER_DATABASES, and ALL_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
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.

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.

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.

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

Execute the stored procedures from SQL Server Agent CmdExec jobs, using sqlcmd and the -b option. Pass the parameters to the stored procedures by name:

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE dbo.DatabaseIntegrityCheck @Databases = 'USER_DATABASES'" -b

Use SQL Server Agent output files to ensure that you always have a full set of information, in case of an error.

You can use the MaintenanceSolution.sql script to install the SQL Server Maintenance Solution. Then the sqlcmd commands and the output files will be configured for you.