593
Chapter 22: Maintaining the Database
22
If the database requires repair, always use the full DBCC CHECKDB rather than one of the
lesser versions:■ (^) DBCC CHECKALLOC ('database'): A subset of DBCC CHECKDB that checks the
allocation of all pages in the database. The report is detailed, listing the extent
count (64KB or eight data pages) and data-page usage of every table and index in
the database.
■ DBCC CHECKFILEGROUP ('filegroup'): Similar to a DBCC CHECKDB but it is
limited to the specifi ed fi legroup only.
■ (^) DBCC CHECKTABLE('table'): Performs physical and logical integrity checks on
the table and all its nonclustered indexes (unless the NOINDEX option is used).
■ DBCC CLEANTABLE ('database', "table"): Reclaims space from a varchar,
nvarchar, text, or ntext column that was dropped from the table.
Data Integrity
Above the physical-structure layer of the database is the data layer, which can be verifi ed
by the following DBCC commands.
■ (^) DBCC CHECKCATALOG ('database'): Checks the integrity of the system tables
within a database, ensuring referential integrity among tables, views, columns,
and data types. Although it reports any errors, under normal conditions no detailed
report is returned. This is also run as a part of DBCC CHECKDB.
■ DBCC CHECKCONSTRAINTS ('table','constraint'): Examines the integrity
of a specifi c constraint, or all the constraints for a table. It essentially generates
and executes a query to verify each constraint and reports any errors found. As
with DBCC CHECKCATALOG, if no issues are detected, nothing is reported.
■ (^) DBCC CHECKIDENT ('table'): Verifi es the consistency of the current identity-
column value and the identity column for a specifi c table. If a problem exists, the
next value for the identity column is updated to correct any error. If the iden-
tity column is broken, the new identity value violates a primary key or unique
constraint and new rows cannot be added to the table. You can also use this
command to reseed the current identity value by using the RESEED option and a
new_reseed_value.
The following code demonstrates the use of the DBCC CHECKIDENT command. If it
is needed, this command resets the current identity value of the Employee table in
the AdventureWorks2012 sample database:
Use AdventureWorks2012;
DBCC CHECKIDENT ("HumanResources.Employee");
Result:
Checking identity information: current
identity value '290', current column value '290'.
DBCC execution completed. If DBCC printed error
messages, contact your system administrator.
c22.indd 593c22.indd 593 7/31/2012 9:24:30 AM7/31/2012 9:24:30 AM
http://www.it-ebooks.info