SQL Server Integrity Check - A Necessary Routine

Today I'll be discussing Integrity Check on SQL Server - we have some very good info in the community, but I'd like to summarize my thoughts here. When a DBA talks about Integrity Check, the conversation is usually redirected to one subject: CheckDB. And yes, we are going to talk about it in today's post. :) The DBCC CheckDB is a medicine of sorts that can prevent headaches, and remedy possible damages. But it should be used with caution: Without a well-planned backup strategy you can lose data. Even with all of the warnings about the importance of Integrity Check, it's not uncommon to see instances without it.
"I'm a Manager just trying to understand this... Could you explain this at a high level?" Let's cut the
blah, blah, blah and go ahead with this... I'll introduce the CheckDB for those who have never heard of it - probably less technical individuals: The CheckDB command is a Database Console Command, or simply DBCC, categorized as a Validation DBCC. This is a necessary step in the entire instance maintenance, where we can detect, and in some cases repair a possible problem with the database integrity. Running the DBCC CheckDb regularly will help to anticipate problems, and even a unexpected shutdown of the instance. We'll go into more detail a bit later...
"What's the objective of the CheckDB?" CheckDB has two roles: The main objective is the integrity check itself, and the second is the correction of the findings. Please not that the CheckDB verifies the database
structure and not the data consistency.
Integrity Check: The command CheckDB follows a few steps on its execution, passing from the following phases:
- Verification of the allocated structures, i.e. GAM, SGAM, PFS, IAM...
- Verification of table consistency, including all of its indexes.
- Verification of the system catalogs.
- PFS pages, system tables and data purity detected errors aren't fixable by CheckDB.
- To repair using CheckDB, the database should be on SINGLE_USER mode.
- Fix the permission to create the snapshot.
- Execute the CheckDB with the TABLOCK option. This can bring problems, as the DB will be a target of locks.
- Create a process (job) doing the following steps:
- Create a snapshot of the database.
- Run the DBCC CheckDB on this snapshot. Yes, the DBCC CheckDB can run over databse snapshots. And it's obvious that a snapshot of a snapshot won't be created.
- SQL Server maintenance plan ignores the TempDB, but if the TempDB becomes corrupt the instance will shutdown. Use caution with this.
- Executing the CheckDB on the master database will cause the execution on the mssqlsystemresource database as well.
- NO_INFOMSGS: Avoid the output of info messages.
- ALL_ERRORMSDGS: Allow the output of error messages.
- Default since SQL Server 2008 SP1.
- NOINDEX: Skips nonclustered indexes verification.
- DATA_PURITY: Validates de data based on the column characteristcs.
- Default since SQL Server 2005.
- ESTIMATEONLY: Estimates the space needed on TempDB.
- As per Paul Randal, this option is broken on newer versions of SQL Server.
- TABLOCK: Uses locks instead of database snapshots.
- Useful when the snapshot creation is failing.
- EXTENDED_LOGICAL_CHECKS: Allows the validation on XML columns and Spatial indexes.
- This can be costly.
- PHYSICAL_ONLY: Skips most of the logical verifications.
- Need less resources to run, but a "full" execution should be sone periodically.
- CHECKALLOC
- CHECKTABLE
- CHECKCATALOG
- CHECKFILEGROUP