590
Part V: Enterprise Data Management
successful completion, column-value integrity checks are enabled for the database, and
you do not need to use the DATA_PURITY option for future DBCC CHECKDB executions.
Column-value integrity checks are enabled by default starting from SQL Server 2005.To reduce blocking and concurrency problems when integrity checks are done,
DBCC CHECKDB uses an internal database snapshot to perform the checks. If a snapshot
cannot be created or you use the TABLOCK option, DBCC CHECKDB uses locks, which
include a short-term exclusive lock on the database. The TABLOCK option can reduce the
time it takes for DBCC CHECKDB to run during peak usage, but it reduces the concurrency
on the database. If you use the TABLOCK option, DBCC CHECKCATALOG is skipped and
Service Broker data is not checked.Review the percent_complete and command columns of sys.dm_exec_requests catalog view to display
the progress and current phase of DBCC CHECKDB.Repairing the Database
When an error is found and DBCC CHECKDB can fi x it, DBCC CHECKDB indicates the repair
level needed to repair the error. When an error is reported by DBCC CHECKDB, it is recom-
mended to restore the database from a known good backup. Use the repair option with
DBCC CHECKDB only when there is no known good backup. Repairing the database is a
separate operation from the normal DBCC CHECKDB command because the database needs
to be placed in single-user mode with the ALTER DATABASE command before a
DBCC CHECKDB can be executed with the repair option. The following command places
the AdventureWorks2012 sample database in single user mode.ALTER DATABASE AdventureWorks2012 SET SINGLE_USER;More on the Single User Mode
The previous command waits indefi nitely if there is a lock on the database or if there are users connected
to the database. You can use a termination clause WITH ROLLBACK AFTER integer [SECONDS] or
WITH ROLLBACK IMMEDIATE to indicate to SQL Server to roll back incomplete transactions after the
specifi ed number of seconds or roll back immediately and close any active connections to the data-
base. The following command places the AdventureWorks2012 sample database in single user mode
and indicates SQL Server to roll back all incomplete transactions immediately and close any active
connections to the database.ALTER DATABASE AdventureWorks2012 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;c22.indd 590c22.indd 590 7/31/2012 9:24:29 AM7/31/2012 9:24:29 AM
http://www.it-ebooks.info