Chapter 12. DBCC validation

The DBCC group of commands, of which there are more than 20, are grouped into four categories: informational, validation, maintenance, and miscellaneous. In versions of SQL Server prior to 2000, DBCC stood for Database Consistency Check and consisted of a smaller set of commands (which are included in the current validation category). Over time, the DBCC command set grew to include more and more functionality, and the acronym was updated to Database Console Commands, reflecting these commands' ability to do more than just validation.

This chapter's focus will be on the commands found in the validation category, sometimes referred to as the DBCC CHECK* commands. Used to report on the integrity of objects in a given database, these commands provide peace of mind that the database is free from corruption, usually caused by faulty disk I/O hardware or software. In combination with a good backup/restore design, regular DBCC validation checks form a crucial part of a database maintenance strategy.

We begin this chapter with an overview of the various DBCC commands before concentrating on the validation group. We then move on to look at the causes of database corruption and techniques for preventing and detecting corruption.

In closing the chapter, we concentrate on techniques for reducing the impact of running CHECKDB on production databases and some important considerations (and alternatives) for repairing corruption with DBCC.