Monitoring Databases  «Prev  Next»

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

Monitor Databases Using dbcc

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:
  1. dbcc checkalloc – Checks the data pages allocated to tables and indexes
  2. dbcc checkcatalog – Verifies SQL Server system tables are correct
  3. dbcc checkdb – Checks the data pages allocated to all tables and indexes and checks structural integrity of the database
  4. dbcc checkfilegroup – Validates the data in a file group
  5. dbcc checkident – Validates a tables identity column
  6. dbcc checktable – Checks the data pages allocated to tables and indexes
  7. dbcc show_contig – Displays fragmentation information about a table
  8. 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.
The syntax is as follows:
  1. The name of the database to check.
  2. Causes dbcc checkalloc to skip checking non-clustered indexes.
  3. Performs more complex repairs that can result in data being lost.
  4. Performs minor fast repairs that can be done without any data lost
  5. Besides the minor repairs, it can perform more complex repairs that will not result in data loss.
  6. Causes dbcc checkalloc will display all error messages.
  7. Causes dbcc checkalloc display no informational messages. When using this option, you will only see messages if there are errors.

dbcc Checkalloc Syntax
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.
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:
  1. Index and data pages pointers are correct.
  2. Indexes are stored in the proper (sorted) order.
  3. The data on each page is reasonable.
  4. 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 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.

Scripting and Automation for SQL Server