Quite often when I see a new SQL Server setup, a CHECKDB has never been run on the databases. It’s pretty simple to do, and it _could_ save you some big trouble later on.

What is it?

The command “DBCC CHECKDB(<db name>)” checks the logical and physical integrity of all the objects in the specified database. If corruption has occured for any reason, the DBCC CHECKDB command will find it, and tell you exactly where the problem is. DBCC CHECKDB has a few optional arguments to help you fix your corrupt database. Depending on how bad it is, you may need to use the “REPAIR_ALLOW_DATA_LOSS”, which, as the name applies, will fix  your database, but some data might get lost. You should _always_ try to recover your database using a backup before using the “REPAIR_ALLOW_DATA_LOSS” argument.
Other corruptions which can be fixed without data loss, could be inconsistency between the table and the indexes. Let’s say one of your nonclustered indexes for some reason miss a couple of rows – then a index rebuild will fix it. This argument is called REPAIR_REBUILD.

How do you use it?

Running DBCC CHECKDB is as simple as this:

DBCC CHECKDB(AdventureWorks2008R2)
GO

Default it will output a lot of informational stuff – here showing the last couple of messages:

DBCC results for 'Production.vProductAndDescription'.
There are 1764 rows in 70 pages for object "Production.vProductAndDescription".
DBCC results for 'sys.syscommittab'.
There are 0 rows in 0 pages for object "sys.syscommittab".
DBCC results for 'Sales.SalesTerritoryHistory'.
There are 17 rows in 1 pages for object "Sales.SalesTerritoryHistory".
CHECKDB found 0 allocation errors and 0 consistency errors in database 'AdventureWorks2008R2'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

If any corruption is detected, the command will raise an error, and highlight the problem. This is the output from a CHECKDB command on a corrupt database:

Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 983674552, index ID 1, partition ID 72057594058571776, 
alloc unit ID 72057594065977344 (type In-row data). Page (1:91587) is missing 
a reference from previous page (1:111583). Possible chain linkage problem.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 983674552, index ID 1, partition ID 72057594058571776, 
alloc unit ID 72057594065977344 (type In-row data). Page (1:111582) was not 
seen in the scan although its parent (1:111431) and previous (1:111581) refer 
to it. Check any previous errors.

The final few lines are:

CHECKDB found 0 allocation errors and 7 consistency errors 
in database 'AdventureWorks2008R2'.
repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKDB (AdventureWorks2008R2).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

In this case I manually messed up the .mdf file by opening it in a hex editor, and zero out some of the bytes. In this case the corruption is so bad, that we need to either fix it by restoring from a backup, or loose data by executing the DBCC CHECKDB (AdventureWorks2008R2) WITH REPAIR_ALLOW_DATA_LOSS. But what if we hadn’t run the CHECKDB command? How would I ever know something is wrong?

Depending on where in your tables the error has occured, you may never notice. In my case the corruption is introduces in a table called HugeData, and if I need to read all data in a table scan like this:

select top 1 * from HugeData
order by FirstName

I receive the following error:

Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect checksum
(expected: 0x07e505f0; actual: 0x386c1019). 
It occurred during a read of page (1:111582) in database ID 6 
at offset 0x000000367bc000 in file 'C:\SQLData\AdventureWorks2008R2_Data.mdf'.  
Additional messages in the SQL Server error log or system event 
log may provide more detail. This is a severe error condition 
that threatens database integrity and must be corrected immediately. 
Complete a full database consistency check (DBCC CHECKDB). 
This error can be caused by many factors; for more information, 
see SQL Server Books Online.

If this table is not one I use every day or even every month, then it might take several weeks from the corruption has occured, until I discover it. And if my backup retention does not go back that far, then I’m in big trouble, and I have no way to recover the lost data.

Running DBCC CHECKDB regularly

Running DBCC CHECKDB on a regular basis will not help you avoid corruption to happen in your database, but it will help you discover it soon enough to be able to recover the lost data using a fresh backup. To schedule this you can either create a maintenance plan to execute the CHECKDB either every day, every week or every 2 weeks. Running CHECKDB puts an extra load on your server, so if you have a 24/7 busy server, you might not want to run it every day on your production server.

Another way is to simply create a SQL Agent job that executes the following command:

exec master.dbo.sp_msforeachdb "DBCC CHECKDB([?]) WITH NO_INFOMSGS, ALL_ERRORMSGS"

The executes the DBCC CHECKDB command on all your databases. If an error occurs in either of them, then the SQL Agent job will fail. And if you have set up an operator and Database Mail, you can actually have SQL Server send you an email when this happens.

How to monitor last CHECKDB?

If you would like to know when your database has last performed a successful CHECKDB, you can use DBCC DBINFO like this:

DBCC DBINFO (AdventureWorks2008R2) WITH TABLERESULTS

This will result in a bunch of information about the database, and one of the rows have Field = ‘dbi_dbccLastKnownGood’ :

To monitor this on all your databases, you can use a script like this to pull the information:

CREATE TABLE #tempTotal
(
    DatabaseName varchar(255),
    Field VARCHAR(255),
    Value VARCHAR(255)
)
CREATE TABLE #temp
(
    ParentObject VARCHAR(255),
    Object VARCHAR(255),
    Field VARCHAR(255),
    Value VARCHAR(255)
)
EXECUTE sp_MSforeachdb '
INSERT INTO #temp EXEC(''DBCC DBINFO ( ''''?'''') WITH TABLERESULTS'')
INSERT INTO #tempTotal (Field, Value, DatabaseName)
SELECT Field, Value, ''?'' FROM #temp
TRUNCATE TABLE #temp';
;WITH cte as
(
    SELECT
        ROW_NUMBER() OVER(PARTITION BY DatabaseName, Field ORDER BY Value DESC) AS rn,
        DatabaseName,
        Value
    FROM #tempTotal t1
    WHERE (Field = 'dbi_dbccLastKnownGood')
)
SELECT
    DatabaseName,
    Value as dbccLastKnownGood
FROM cte
WHERE (rn = 1)
DROP TABLE #temp
DROP TABLE #tempTotal

This will give you a list of all your databases, with a column telling you when a successfull CHECKDB was last run:

Conclusion

If you haven’t done so already, you should make sure to set up a scheduled DBCC CHECKDB task on your SQL Server. This way you will be alerted if/when a database corruption has happened, so you can take action immediately. Without this corruption check, you might not discover the problem until it’s too late. And this is a situation you do not want to find yourself in.