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.

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.

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.

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.

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.