Checks the logical and physical integrity of all the objects in the specified database by performing the following operations:

From <http://technet.microsoft.com/en-us/library/ms176064.aspx>

 

Running DBCC CHECKDB

Always run CHECKDB with NO_INFOMSGS,

If you run CHECKDB without this you will get a wall of informational messages.

DBCC CHECKDB (Blog) WITH NO_INFOMSGS

To run CHECKDB against a database you can either run

DBCC CHECKDB () if you are in the context of the database you want to run against (ie

USE [MyDB]
GO
DBCC CHECKDB () WITH NO_INFOMSGS

Or via naming the database within the DBCC command

DBCC CHECKDB (MyDB) WITH NO_INFOMSGS

 

Highlights

DBCC CheckDB is a resource intensive action. (It checks every page on disk so I/O will be high, but it is also very memory and CPU heavy)

DBCC CHECKDB does not cause blocking locks since SQL Server 2000

If the CHECKDB is ran on Enterprise edition it will run with Parallelism. (This can be good or bad, check each case. It’s possible to turn off with Trace Flag 2528)

You can monitor DBCC CHECKING progress via

sys.dm_exec_requests

You can read the “last known good” to see the last time CHECKDB was ran successfully.

DBCC TRACEON (3604);
DBCC DBINFO (Blog)
GO

The results will show a lot of text, Scroll down to:

dbi_dbccLastKnownGood =

Results will look like below (Note the database I ran it against has never had CHECKDB Ran agaisnt it! You should never see this date in production!!!

dbccbasics1

 

 

Note – Read Erins blog to see that more than just CHECKDB updates this (
DBCC CHECKFILEGROUP, DBCC CHECKDB WITH PJYSICAL_ONLY will as well)

http://www.sqlskills.com/blogs/erin/what-checks-update-dbcclastknowngood/

DBCC CHECKDB runs each of the below DBCC Commands.
DBCC CHECKALLOC
DBCC CHECKTABLE
DBCC CHECCATALOG

 

Resources for CHECKDB:

BOL

http://msdn.microsoft.com/en-us/library/ms176064.aspx

Answer to pretty much every DBCC CHECKDB Question will be in here:

http://www.sqlskills.com/blogs/paul/category/checkdb-from-every-angle/

Erin has blogged about Parallelism with CHECKDB and Maintenance plans for CHECKDB

http://www.sqlskills.com/blogs/erin/category/dbcc/

Jonathan Kehayias has talked about using Resource Governor for lowering CPU for CHECKDB. But I can’t find the article.

Paul has created a video series for Pluralsight on Detecting and Correcting Database Corruption.

http://www.pluralsight.com/training/Courses/TableOfContents/sqlserver-database-corruption

SQL Skills cover corruption in there IE 3 course High Availability and Disaster Recovery

http://www.sqlskills.com/sql-server-training/ie3/

SQL Server Internals 2008 / 2012 books

Advertisements