Microsoft® SQL Server® 2012 Bible

589

Chapter 22: Maintaining the Database

22

DBCC results for 'sys.sysrscols'.

There are 1406 rows in 14 pages for object "sys.sysrscols".

DBCC results for 'sys.sysrowsets'.

There are 263 rows in 3 pages for object "sys.sysrowsets".

DBCC results for 'sys.sysallocunits'.

...

DBCC results for 'Production.ProductModelProductDescriptionCulture'.

There are 762 rows in 4 pages for

object "Production.ProductModelProductDescriptionCulture".

DBCC results for 'Sales.Store'.

There are 701 rows in 101 pages for object "Sales.Store".

DBCC results for 'Production.ProductPhoto'.

There are 101 rows in 50 pages for object "Production.ProductPhoto".

CHECKDB found 0 allocation errors and 0 consistency errors

in database 'AdventureWorks2012'.

DBCC execution completed. If DBCC printed error messages,

contact your system administrator.

The results you see could vary from those displayed in the book depending on your SQL Server confi guration and any

changes you have made to the database.

Two options simply determine which messages are reported, without altering the

functionality of the integrity check: ALL_ERRORMSGS and NO_INFOMSGS. ALL_

ERRORMSGS displays all the error messages; when it is not used, the default displays 200

errors per object. You can use the NO_INFOMSGS to suppress all informational messages.

The ESTIMATEONLY option returns the estimated size of the tempdb required by

DBCC CHECKDB without actually running DBCC CHECKDB against the database.

If the database is large, you can use the NOINDEX option to skip checking the integrity

of all user-table nonclustered indexes. For additional time-savings, the PHYSICAL_ONLY

option performs only the most critical checks on the physical structure of the pages. Use

these options only when time prevents a complete DBCC CHECKDB or when the indexes are

about to be rebuilt.

If you want to perform logical consistency checks on indexed views, XML indexes, and

spatial indexes, use the option EXTENDED_LOGICAL_CHECKS. This option increases the

performance impact on the SQL Server, and currently its progress cannot be tracked. You

should fi rst run this on a similar database on a similar test SQL Server to get an estimate

of the time it takes to run DBCC CHECKDB with this option and the performance impact on

the SQL Server.

For databases upgraded from SQL Server 2000 or earlier, run

DBCC CHECKDB WITH DATA_PURITY as a post-upgrade step to enable the column-value

integrity checks because they are disabled by default on SQL Server 2000 and earlier. After

c22.indd 589c22.indd 589 7/31/2012 9:24:29 AM7/31/2012 9:24:29 AM

http://www.it-ebooks.info