page contents

About the Post

Author Information

DBCC CHECKDB A Further Explanation

In an earlier, post I talked about doing backups and also making sure they are being done with data that has been verified as healthy. We want to avoid the scenario of garbage in leads to garbage out. As stated in that earlier article:

Robert Davis (Blog | Twitter) provided me with some good advice:

You should be running DBCC CheckDB regularly. Daily if your system can handle it, but at least weekly. You don’t need to check for the last good DBCC CheckDB if you are running regular CheckDB. For backups, you should be using the CHECKSUM option of the backup command.

So how do we get there and what tool can we use? The answer is simple we can use the built in tool DBCC CHECKDB.

DBCC CHECKDB checks the logical and physical integrity of all the objects in the specified database. If DBCC CHECKDB is ran on a database please note that you do not need to run DBCC CHECKALLOC, DBCC CHECKTABLE, and DBCC CHECKCATALOG since DBCC CHECKDB includes all the three command. Usage of these built-in DBCC commands is listed below.

DBCC CHECKALLOC – Checks the consistency of disk space allocation structures for a specified database.

DBCC CHECKTABLE – Checks the integrity of all the pages and structures that make up the table or indexed view.

DBCC CHECKCATALOG – Checks for catalog consistency within the specified database. The database must be online.

Along with above three DBCC commands it also runs following two tasks to check the validity of the database both physical as well logical. If the DBCC check has returned any errors on a production database, the best solution is to RESTORE your DATABASE from BACKUP. If you’re unable to schedule a full DBCC CHECKDB in a maintenance window, you can add WITH CHECKSUM to your backup routines, and do full CHECKDB’s at a different time (SQL 2005 and later).

Note that a BACKUP […] WITH CHECKSUM does not replace DBCC CHECKDB. Paul Randal has more details here.

Tags: , , , ,

2 Responses to “DBCC CHECKDB A Further Explanation”

  1. As you said if DBCC Checkdb returns error message for production database then the best solution is restore database from latest backup but what to do in case of unavailability of latest backup.

    DBCC Checkdb offers two options to repair & restore when it reports error messages; ‘repair_rebuild’ & ‘repair_allow_data_loss’. Repair_rebuild option tries to repair database if corruption level is low & Repair_allow_data_loss tries to repair database when corruption level is high but you may lose some your important data.

    If you don’t want to loss any single data of your database then it is recommended to use Microsoft Gold satisfied partner’s product like Stellar Phoenix SQL recovery.

    Thanks

    September 10, 2012 at 11:12 PM

Trackbacks/Pingbacks

  1. Update to an earlier article... Ola Recommendations for his script | Scott Mattie's Blog - September 26, 2012

    […] […]

Copy Protected by Chetan's WP-Copyprotect.