DBCC CHECKDB checks the logical and physical integrity of all objects in the specified database by performing the following:
SERVICE BROKER Data is Verified:
DBCC will run faster on a SQL 2005 database upgraded from 2000 but with no 2005 features or indexed views. On a new 2005 database, some of the logical checks added to compliment new features are necessarily complex and add to the runtime when invoked.
All the DBCC validation commands use database snapshots to keep validation operations from interfering with on-going database operations and to allow the validation operation to see a quiescent, consistent view of the the data. A snapshot is created at the beginning of the CHECK command, and no locks are acquired on any of the objects being checked. The actual check operation is performed against the snapshot. Unlike regular snapshots, the "snapshot file" that DBCC CHECKDB creates cannot be configured and is invisible to the end user. It always uses apace on the same volume as the database being checked. This capability is only available when your data directory is on an NTFS partition.
SQL Server 2005 includes a set of logical validity checks to verify that data is appropriate for the columns data type. These can be expensive and affect server performance, so you can disable these, along with other non-core logical validations by using the PHYSICAL-ONLY option. All new databases in SQL Server 2005 have the DATA_PURITY logical validations enabled by default. For upgraded databases, you must run the DBCC CHECKDB with the DATA_PURITY option once, preferably immediately after the upgrade
DBCC CHECKDB ('dbname') WITH DATA_PURITY
After the purity check completes without any errors, performing the logical validations is the default behavior in all future executions of DBCC CHECKDB and there is no way to change this default. You can override the default by using the PHYSICAL_ONLY option. This skips the data purity check but also skips any checks that have to analyze the contents of individual rows of data and limits the checks that DBCC performs.
DBCC REPAIR OPTIONS
There are three DBCC repair options:
Almost all errors can be repaired except:
When you run DBCC CHECKTABLE with one of the repair options:
Use repair options only as a last resort, restore the database from backup instead. If you are going to run repair_allow_data_loss, backup your database first. You can run the repair options for DBCC inside a user-defined transaction, which means you can perform a ROLLBACK to undo the repairs. Exception is when you are running the repair options on a database in EMERGENCY mode. If a repair in emergency mode fails, there are no other options except to restore the database from a backup.
Each individual repair in a DBCC operation runs in it's own system transaction, which means that if a repair is not possible, it will not affect any other repairs.
Create a snapshot before the repair is initiated, start a transaction, then run the REPAIR option. Before committing or rolling back, you can compare the repaired database with the original snapshot. If you are not satisfied with the changes made, rollback the repair operation.
DBCC BEST PRACTICES
Use CHECKDB with CHECKSUM option and a sound backup strategy to protect the integrity of the database from hardware failures
Perform DBCC CHECKDB WITH DATA_PURITY after upgrading a database to SQL Server 2005 to check for invalid data values
Make sure you have enough disk space available to accommodate the database snapshot
Make sure you have space available ion tempdb to allow the DBCC command to run. Use the ESTIMATEONLY command to find out how much space is required in tempdb
Use REPAIR_ALLOW_DATA_LOSS only as a last resort
This is an excellent article, very helpful. Appreciated.
Starting SQL 2014, the snapshot file is visible. If the database MDF is DBtst.mdf, the snapshot will be in the same folder named as DBtst.mdf_MSSQL_DBCC6.