Lesson 4	Monitor databases with dbcc
Objective	Use dbcc to monitor a database.

Monitor Databases Using dbcc

Does SQL-Server still use dbcc to monitor a database.

Previously, you learned about the SQL Server command called database consistence checker (dbcc) that can be used to monitor SQL Server’s database, ensuring that SQL Server’s internal structures are valid.
The next three lessons will show you nine different ways to use dbcc to monitor and maintain SQL Server databases, including:

dbcc checkalloc – Checks the data pages allocated to tables and indexes
dbcc checkcatalog – Verifies SQL Server system tables are correct
dbcc checkdb – Checks the data pages allocated to all tables and indexes and checks structural integrity of the database
dbcc checkfilegroup – Validates the data in a file group
dbcc checkident – Validates a tables identity column
dbcc checktable – Checks the data pages allocated to tables and indexes
dbcc show_contig – Displays fragmentation information about a table
dbcc dbreindex – Rebuilds the indexes on a table

In this lesson, we will begin by looking at dbcc checkalloc, dbcc checkdb, and dbcc checkcatalog.

dbcc checkalloc

The dbcc checkalloc command is used to check that all of the pages in the database are correctly assigned. If there are any errors, you can also use this command to fix the database.

Errors produced by dbcc Procedures

If any dbcc procedure produces an error, it means that SQL Server did not store your data correctly, or that it has incorrectly assigned storage to tables. When they do generate an error, you should use the dbcc with the Fix options to correct the errors.

Expand Minimize

DBCC CHECKDB
 SQL Server 2000  

Checks the allocation and structural integrity of all the objects in the specified database.
Syntax

DBCC CHECKDB 
     ( 'database_name' 
             [ , NOINDEX 
                 | { REPAIR_ALLOW_DATA_LOSS 
                     | REPAIR_FAST 
                     | REPAIR_REBUILD 
                     } ] 
     )    [ WITH { [ ALL_ERRORMSGS ] 
                     [ , [ NO_INFOMSGS ] ] 
                     [ , [ TABLOCK ] ] 
                     [ , [ ESTIMATEONLY ] ] 
                     [ , [ PHYSICAL_ONLY ] ] 
                     } 
         ] 

Arguments

'database_name'

Check all Object Allocation and Structural Integrity

Is the database for which to check all object allocation and structural integrity. If not specified, the default is the current database.
Database names must conform to the rules for identifiers. For more information, see Using Identifiers.

NOINDEX

Specifies that nonclustered indexes for nonsystem tables should not be checked. NOINDEX decreases the overall execution time because it does not check nonclustered indexes for user-defined tables. NOINDEX has no effect on system tables, because DBCC CHECKDB always checks all system table indexes.

REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST| REPAIR_REBUILD

Specifies that DBCC CHECKDB repair the found errors. The given database_name must be in single-user mode to use a repair option and can be one of the following.

The syntax is as follows:

DBCC CHECKALLOC Syntax

DBCC CHECKALLOC ('database'): A subset of DBCC CHECKDB that checks the allocation of all pages in the database. The report is very detailed, listing the extent count (64KB or eight data pages) and data-page usage of every table and index in the database.

database name – The name of the database to check.
NONINDEX – Causes dbcc checkalloc to skip checking non-clustered indexes.
REPAIR_ALLOW_DATA_LOSS – Performs more complex repairs that can result in data being lost.
REPAIR_FAST – Performs minor fast repairs that can be done without any data lost.
REPAIR_REBUILD – Besides the minor repairs, it can perform more complex repairs that will not result in data loss.
ALL_ERRORMSGS – Causes dbcc checkalloc will display all error messages.
NO_INFOMSGS – Causes dbcc checkalloc display no informational messages. When using this option, you will only see messages if there are errors.

Dbcc checkalloc will validate every table and every data page in the database, and will display information about every table and will provide messages about errors it finds. If you do not specify either ALL_ERRORMSGS or NO_INFOMSGS, it will display up to 200 errors per table.

dbcc checkalloc output

When running dbcc checkalloc, you will see an output similar to this one:

dbcc checkalloc
go
…. 

***************************************************************
Table employee                Object ID 693577509.
Index ID 1         FirstIAM (1:131)   Root (1:132)    Dpages 1     Sort 0.
    Data level 1. 3 data pages in 0 extents.
Index ID 2         FirstIAM (1:134)   Root (1:133)    Dpages 1     Sort 0.
    Index ID 2. 2 index pages in 0 extents.
Total number of extents is 0.
***************************************************************

Table a1                Object ID 965578478.
Index ID 0         FirstIAM (1:162)   Root (1:161)    Dpages 1     Sort 0.
    Data level 1. 2 data pages in 1 extents.
Total number of extents is 1.

***************************************************************

Processed 44 entries in sysindexes for database ID 5.

Allocation page (1:2). Number of extents = 22, used pages = 149, referenced pages = 100.

           (1:2) (number of mixed extents = 18, mixed pages = 134).
. . .

Object ID 645577338, Index ID 1, data extents 0, pages 3, mixed extent pages 3.

Object ID 645577338, Index ID 255, index extents 1, pages 14, mixed extent pages 9.

Object ID 693577509, Index ID 1, data extents 0, pages 3, mixed extent pages 3.

Object ID 693577509, Index ID 2, index extents 0, pages 2, mixed extent pages 2.

Object ID 965578478, Index ID 0, data extents 1, pages 2, mixed extent pages 1.

Total number of extents = 22, used pages = 149, referenced pages = 100 in this database.

(number of mixed extents = 18, mixed pages = 134) in this database.

CHECKALLOC found 0 allocation errors and 0 consistency errors in database 'pubs'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator

The output will contain a section for every table in the database, describing the space allocated to the table along with any errors. This will be followed by a one-line report for each table. If you are going to use dbcc checkalloc to fix errors, you need to have the database in single user mode. Single user mode: A database that is configured to allow only one user to access it.

dbcc checkdb

Like dbcc checkalloc, the dbcc checkdb command is used to check that all of the pages in the database are correctly assigned. If there are any errors, it can be used to fix the database. Notice that the optional parameters are the same as those in dbcc checkalloc.

DBCC CHECKDB ( 'database_name'

  [, NOINDEX | 

   { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST| REPAIR_REBUILD}]

   ) [WITH {ALL_ERRORMSGS | NO_INFOMSGS}]

The procedure dbcc checkdb can repair everything the dbcc checkalloc can, plus it can perform more detailed checks. dbcc checkdb will validate the pages allocated to every table page allocation, and it will check the following, which are not checked by dbcc checkalloc:

Index and data pages pointers are correct.
Indexes are stored in the proper (sorted) order.
The data on each page is reasonable.
Page offsets are reasonable.

dbcc checkdb is the perferred method over dbcc checkalloc. It does a much more thorough job.
By default, it will report on every table in the database.

dbcc checkdb Output in SQL-Server

The following is an excerpt from the output of dbcc checkdb:

There are 21 rows in 1 pages for object 'sales'.
DBCC results for 'roysched'.

There are 86 rows in 1 pages for object 'roysched'.

DBCC results for 'discounts'.

There are 3 rows in 1 pages for object 'discounts'.

DBCC results for 'jobs'.

There are 14 rows in 1 pages for object 'jobs'.

DBCC results for 'pub_info'.

There are 8 rows in 1 pages for object 'pub_info'.

DBCC results for 'employee'.

There are 43 rows in 1 pages for object 'employee'.

DBCC results for 'a1'.

There are 23 rows in 1 pages for object 'a1'.

CHECKDB found 0 allocation errors and 0 consistency errors in database 'pubs'.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Scripting and Automation for SQL Server

dbcc checkcatalog

The dbcc checkcatalog command is used to verify the consistency of SQL Server system tables. It verifies syscolumns, systypes, and sysviews. The syntax is as follows:

DBCC CHECKCATALOG ( 'database_name') [WITH NO_INFOMSGS]

If there are no errors, dbcc checkcatalog will not return any messages

. 
Dbcc checkcatalog

will only report onerrors, it does not fix any errors.
In the next lesson, you will continue your exploration of the dbcc command and learn how to use it to validate individual tables.