--- PDF PAGE 324 ---
308
DBCC CHECKDB
DBCC CHECKDB is a utility that can be used to both discover corruption and also fix the 
errors. When you run DBCC CHECKDB, by default it creates a database snapshot and runs 
the consistency checks against this snapshot. This provides a transactionally consistent 
point from which the checks can occur while at the same time reducing contention in 
the database. It can check multiple objects in parallel to improve performance, but this 
depends on the number of cores that are available and the MAXDOP setting of the instance.
Checking for Errors
When you run DBCC CHECKDB for the purpose of discovering corruption only, then you 
can specify the arguments, detailed in Table 9-5.
Table 9-5.  DBCC CHECKDB Arguments
Argument
Description
NOINDEX
Specifies that integrity checks should be performed on heap and clustered 
index structures but not on nonclustered indexes.
EXTENDED_
LOGICAL_CHECKS
Forces the logical consistency of XML indexes, indexed views, and spatial 
indexes to be performed.
NO_INFOMSGS
Prevents informational messages from being returned in the results. This 
can reduce noise when you are searching for an issue, since only errors 
and warnings with a severity level greater than 10 are returned.
TABLOCK
DBCC CHECKDB creates a database snapshot and runs its consistency 
checks against this structure to avoid taking out locks in the database, 
which cause contention. Specifying this option changes that behavior so 
that instead of creating a snapshot, SQL Server takes out a temporary 
exclusive lock on the database, followed by exclusive locks on the 
structures that it is checking. In the event of high write load, this can reduce 
the time it takes to run DBCC CHECKDB, but at the expense of contention 
with other processes that may be running. It also causes the system table 
metadata validation and service broker validation to be skipped.
(continued)
Chapter 9  Database Consistency

--- PDF PAGE 325 ---
309
DBCC CHECKDB is a very intensive process that can consume many CPU and IO 
resources. Therefore, it is advisable to run it during a maintenance window to avoid 
performance issues for applications. The Database Engine automatically decides 
how many CPU cores to assign the DBCC CHECKDB based on the instance level setting 
for MAXDOP and the amount of throughput to the server when the process begins. If 
you expect load to increase during the window when DBCC CHECKDB will be running, 
however, then you can throttle the process to a single core by turning on Trace Flag 
2528. This flag should be used with caution, however, because it causes DBCC CHECKDB 
to take much longer to complete. If a snapshot is not generated, either because you have 
specified TABLOCK or because there was not enough space on disk to generate a snapshot, 
then it also causes each table to be locked for a much longer period.
The sample in Listing 9-8 does not perform any checks but calculates the amount 
of space required in TempDB in order for DBCC CHECKDB to run successfully against the 
Chapter9 database.
Argument
Description
ESTIMATEONLY
When this argument is specified, no checks are performed. The only thing 
that happens is that the space required in TempDB to perform the checks is 
calculated based on the other arguments specified.
PHYSICAL_ONLY
When this argument is used, DBCC CHECKDB is limited to performing 
allocation consistency checks on the database, consistency checks on 
system catalogs, and validation on each page of every table within the 
database. This option cannot be used in conjunction with DATA_PURITY.
DATA_PURITY
Specifies that column integrity checks are carried out, such as ensuring 
that values are within their data type boundaries. For use with databases 
that have been upgraded from SQL Server 2000 or below only. For any 
newer databases, or SQL Server 2000 databases that have already been 
scanned with DATA_PURITY, the checks happen by default.
ALL_ERRORMSGS
For backward compatibility only. Has no effect on SQL 2019 databases.
Table 9-5.  (continued )
Chapter 9  Database Consistency

--- PDF PAGE 336 ---
320
If you have a partitioned table, which is stored on multiple filegroups, DBCC 
CHECKFILEGROUP only checks the consistency of the partition(s) that are stored on the 
filegroup being checked. The arguments for DBCC CHECKFILEGROUP are the same as those 
for DBCC CHECKDB, with the exception of DATA_PURITY, which is not valid and you cannot 
specify any repair options. You also need to specify the filegroup name or ID.
DBCC CHECKIDENT
DBCC CHECKIDENT scans all rows within a specified table to find the highest value in the 
IDENTITY column. It then checks to ensure that the next IDENTITY value, which is stored 
in a table’s metadata, is higher than the highest value in the IDENTITY column of the 
table. DBCC CHECKIDENT accepts the arguments detailed in Table 9-6.
We could check the IDENTITY value against the maximum IDENTITY value in our 
CorruptTable table by using the command in Listing 9-13.
Listing 9-13.  DBCC CHECKIDENT
DBCC CHECKIDENT('CorruptTable',NORESEED) ;
Table 9-6.  DBCC CHECKIDENT Arguments
Argument
Description
Table Name
The name of the table to be checked.
NORESEED
Returns the maximum value of the IDENTITY column and the current 
IDENTITY value, but will not reseed the column, even if required.
RESEED
Reseeds the current IDENTITY value to that of the maximum IDENTITY 
value in the table.
New Reseed Value
Used with RESEED, specifies a seed for the IDENTITY value. This should 
be used with caution, since setting the IDENTITY value to lower than the 
maximum value in the table can cause errors to be generated, if there is 
a primary key or unique constraint on the IDENTITY column.
WITH NO_INFOMSGS
Causes informational messages to be suppressed.
Chapter 9  Database Consistency

--- PDF PAGE 337 ---
321
The results, displayed in Figure 9-15, show that both the maximum value in the 
IDENTITY column and the current IDENTITY value are both 10201, meaning that there is 
not currently an issue with the IDENTITY value in our table.
DBCC CHECKCONSTRAINTS
DBCC CHECKCONSTRAINTS can check the integrity of a specific foreign key or check 
constraint within a table, check all constraints on a single table, or check all 
constraints on all tables of a database. DBCC CHECKCONSTRAINTS accepts the arguments 
detailed in Table 9-7.
Figure 9-15.  DBCC CHECKIDENT results
Table 9-7.  DBCC CHECKCONSTRAINTS Arguments
Argument
Description
Table or Constraint
Specifies either the name or ID of the constraint you wish to check or 
specifies the name or ID of a table to check all enabled constraints on that 
table. Omitting this argument causes all enabled constraints on all tables 
within the database to be checked.
ALL_CONSTRAINTS
If DBCC CHECKCONSTRAINTS is being run against an entire table or 
entire database, then this option forces disabled constraints to be checked 
as well as enabled ones.
ALL_ERRORMSGS
By default, if DBCC CHECKCONSTRAINTS finds rows that violate a 
constraint, it returns the first 200 of these rows. Specifying ALL_
ERRORMSGS causes all rows violating the constraint to be returned, even if 
this number exceeds 200.
NO_INFOMSGS
Causes informational messages to be suppressed.
Chapter 9  Database Consistency