DBCC CheckDB

DBCC CHECKDB runs all of the other DBCC validation commands in this order:

DBCC CHECKALLOC is run on the database;
DBCC CHECKTABLE is run on every table and indexed view in the database;
DBCC CHECKCATALOG is run on the database;
The Service Broker data in the database is verified.

In SQL Server 2005, all of the DBCC validation commands use database snapshot technology to keep the validation operation from interfering with ongoing database operations and to allow the validation operation to see a quiescent, consistent view of the data, no matter how many changes were made to the underlying data while the operation was under way. A snapshot of the database 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 executed against the snapshot.
The "snapshot file" that DBCC CHECKDB creates with the original page images is not visible to the end user and its location cannot be configured; it always uses space on the same volume as the database being checked. This capability is available only when your data directory is on an NTFS partition.
If you aren't using NTFS, or if you don't want to use the space necessary for the snapshot, you can avoid creating the snapshot by using the WITH TABLOCK option with the DBCC command.
In addition, if you are using one of the REPAIR options to DBCC, a snapshot is not created because the database is in single-user mode, so no other transactions can be altering data. Without the TABLOCK option, the DBCC validation commands are considered online operations because they don't interfere with other work taking place in a database. With the TABLOCK option, however, a Shared Table lock is acquired for each table as it processed, so concurrent modification operations will be blocked.

For databases that have been upgraded from previous SQL Server versions, you must run DBCC CHECKDB with the DATA_PURITY option once, preferably immediately after the upgrade, as follows:

DBCC CHECKDB (<db_name>) WITH DATA_PURITY

SQL Server 2005 includes a set of logical validation checks to verify that data is appropriate for the column's datatype. These checks can be expensive and can affect the server's performance, so you can choose to disable this, along with all the other non-core logical validations by using the PHYSICAL_ONLY option. All new databases created in SQL Server 2005 have the DATA_PURITY logical validations enabled by default.

After the purity check completes without any errors for a database, 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, of course, override the default with the PHYSICAL_ONLY option. This option not only skips the data purity checks, but it also skips any checks that actually have to analyze the contents of individual rows of data and basically limits the checks that DBCC performs to the integrity of the physical structure of the page and the row headers.

If the CHECKSUM option is enabled for a database, which is the default in all new SQL Server 2005 databases, a checksum will be performed on each allocated page as it is read by the DBCC CHECK commands. When the CHECKSUM option is on, a page checksum is calculated and written on each page as it is written to disk, so only pages that have been written since CHECKSUM was enabled will have this check done. The page checksum value is checked during the read and compared with the original checksum value stored on the page. If they do not match, an error is generated. In addition, pages with errors are recorded in the suspect_pages table in the msdb database.

Using the following script to see the progress reporting for DBCC CHECKDB, DBCC CHECKTABLE, and DBCC CHECKFILEGROUP:

select command,percent_complete,estimated_completion_time

from sys.dm_exec_requests
where command like 'dbcc %'

DBCC CHECKDB Best Practices:

· Use CHECKDB with the CHECKSUM database option and a sound backup strategy to protect the integrity of the data from hardware-caused corruption.

· Perform DBCC CHECKDB with the DATA_PURITY option after upgrading a database to SQL 2005 to check for invalid data values.

· Make sure there is enough disk space availabe to accommodate the database snapshot that will be created.

· Make sure there is enough space available in tempdb to allow the DBCC CHECKDB command to run.

SET NOCOUNT ON;
DBCC CHECKDB ('<DB NAME>') WITH ESTIMATEONLY;
use tempdb;
sp_helpfile;

You can see the current size of the log file for all databases, as well as the percentage of the log file space that has been used, by running the DBCC command DBCC SQLPERF('logspace'). The following code, supplied by Cliff Dibble from Microsoft, returns the same information as DBCC SQLPERF('logspace') in a tabular format that can be further filtered or easily embedded into a stored procedure, table-valued function, or view:

  SELECT rtrim(pc1.instance_name) AS [Database Name]

   ,      pc1.cntr_value/1024.0 AS [Log Size (MB)]

   ,      cast(pc2.cntr_value*100.0/pc1.cntr_value as dec(5,2))

           as [Log Space Used (%)]

   FROM    sys.dm_os_performance_counters as pc1

   JOIN    sys.dm_os_performance_counters as pc2

   ON      pc1.instance_name = pc2.instance_name

   WHERE   pc1.object_name LIKE '%Databases%'

   AND     pc2.object_name LIKE '%Databases%'

   AND     pc1.counter_name = 'Log File(s) Size (KB)'

   AND     pc2.counter_name = 'Log File(s) Used Size (KB)'

   AND     pc1.instance_name not in ('_Total', 'mssqlsystemresource')

   AND     pc1.cntr_value > 0

go

The final condition is needed to filter out databases that have no log file size reported. This includes any database that is unavailable because it has not been recovered or is in a suspect state, as well as any database snapshots, which have no transaction log.