Presents your 
SQL SERVER E-NEWSLETTER for August 5, 2003

<------------------------------------------->

CHECK DATABASE INTEGRITY WITH DBCC CHECKDB

It's vital to check the integrity of your database before backing it up
because a bad backup is virtually no backup. In order to verify the
consistency of your database, use the Database Consistency Checker
(DBCC).
Here's a run-through of some of the DBCC commands:

* DBCC CHECKDB checks everything in your database and has several
options for determining how and what to check in your database. The DBCC
statement collects information and then scans the log for any additional
changes made to your database, merging the two sets of information to
produce a consistent view of your data.

* NOINDEX, which is only used for backward capability, checks your
database but doesn't check unclustered indexes. NOINDEX also applies to
any secondary indexes on indexed views.

* REPAIR_ALLOW_DATA_LOSS cleans up broken pages and text/image pages
regardless of whether this causes data loss. (The database must be in
single user mode to use this option.)

* REPAIR_FAST repairs your database quickly without allowing for data loss.

* ALL_ERRORMSGS returns all error messages.

* NO_INFOMSGS only reports really important error messages. The DBCC
CHECKDB statement below returns a much more streamlined result set:

DBCC CHECKDB WITH NO_INFOMSGS
GO
The command(s) completed successfully.

In the example below, we're going to check the pubs database without
information on clustered indexes:

DBCC CHECKDB ('pubs', NOINDEX)
GO

You should get a hefty result set back that looks something like this:

DBCC results for 'pubs'.
Warning: NO_INDEX option of CHECKDB being used. Checks on non-system
indexes will be skipped.
DBCC results for 'sysobjects'.
There are 117 rows in 3 pages for object 'sysobjects'.
...
DBCC results for 'titles'.
There are 18 rows in 1 pages for object 'titles'.
CHECKDB found 0 allocation errors and 0 consistency errors in database 'pubs'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.

J.E. Harvey, MCSD, MCDBA, has been tinkering with and writing about
technology for more than two decades.

----------------------------------------