WyoRock

By Arthur Fuller
December 21, 2006, 8:00am PST

In moments of distress, the database consistency check (DBCC) may prove to be your most valuable friend. This tip provides a thumbnail sketch of the things DBCC can do for you, which include the following:

Check the integrity of your tables and their related indexes.
Check the whole database.
Check the integrity of database pages.
Rebuild the indexes on any given table.

Why you need to befriend DBCC

If you are wondering why using DBCC is even necessary, here are the reasons:

Database pages (both tables and indexes) need to be split from time to time, which can result in bad allocations.
Indexes can become corrupted or simply inefficient.
The SQL Server engine can occasionally misunderstand your intentions.
In situations where a large number of updates is the norm, things can get hairy (remember that any given update is actually a delete plus an insert).
Individual pages, while still "sound," may lose their optimal storage footprint.

How to run DBCC

You can run DBCC in two ways: from a command window and from inside a Query Analyzer window. You can also schedule its operations if you deem it necessary. (I have never felt the need to do this because, of all Microsoft's products, I am most confident in the stability of SQL Server. I believe that it is the finest product ever to emerge from Redmond. But, things can still go wrong.)

The DBCC command has the following extensions:

CheckDB: checks the consistency of the entire database, and is the basic method to check for database corruption.
CheckTable: checks a specified table for problems.
CheckAlloc: checks the individual pages allocated to a database, both tables and indexes.
Reindex: rebuilds the indexes on a specified table.
CacheStats: tells you about the objects currently stored in the memory cache.
DropCleanBuffers: drops all the data currently stored in the buffer, so that you can continue testing without using the previous results.
Errorlog: erases (truncates) the current log. You might consider scheduling a job consisting of this command to run once a week or so.
FlushProcInDB: clears out the stored procedure cache for the specified database (use its dbid not its name). Discover its id using:

SELECT dbid FROM master.dbo.sysdatabases
WHERE name = '<name your poison>

IndexDefrag: reduces fragmentation in indexes without imposing a lock on the files so that users can continue working with the database.
CheckCatalog: checks the specified database for consistency in tables and between tables (the latter means foreign keys etc.).

How to use five of these extensions

DBCC first creates a snapshot of your database (except in certain special circumstances, such as working with a Master, TempDB, or read-only database). A proviso: In order to use DBCC, your database must be in single-user mode.

Using DBCC CheckDB
This command ensures that:

Data and index pages are correctly linked.
Indexes are sorted correctly and are up to date.
Pointers are consistent.
Data on each page is up to date.
Page offsets are up to date.

Here are three of the most common ways to use CheckDB:

DBCC CHECKDB ('AdventureWorks', REPAIR_FAST)
DBCC CHECKDB ('AdventureWorks', REPAIR_REBUILD)
DBCC CHECKDB ('AdventureWorks', REPAIR_ALLOW_DATA_LOSS)

There are several other options that you may specify, but these are the three critical options. I have presented the DBCC commands in the order in which you should run them and then check the results afterwards. The first two options will not result in a loss of data, while the third will cause data loss. It's advisable to place the third command inside a transaction so you can perform a ROLLBACK if the data loss is unacceptable.

Using DBCC CheckTable
Any problems you encounter will most often be with one or more tables within a database rather than the entire database. In these cases, run DBCC CheckTable. First, use the database of interest and then run the DBCC CheckTable command. Here are two examples:

DBCC CheckTable ('Sales,SalesOrderHeader')
DBCC CheckTable ('Sales,SalesOrderHeader', REPAIR_REBUILD)

Using DBCC CheckAlloc
This command checks the consistency of data pages and their indexes. Here are two examples:

DBCC CHECKALLOC ('Sales.SalesOrderDetails')
DBCC CHECKALLOC ('Sales.SalesOrderDetails', REPAIR_REBUILD)

Using DBCC CheckCatalog
Use this command to verify the consistency of a database's system tables. You specify the name of the database to check and optionally the argument WITH NO_INFOMSGS. Here is an example:

DBCC CHECKCATALOG ('AdventureWorks')

Using DBCC ReIndex
This command forces the reconstruction of one or more indexes on a given table or view. You can also supply the name of a particular index, as well as a fill factor. Listing A contains two examples. The third argument specifies that I want a fill factor of 90% on the recreated index.