We are currently moving towards a 24/7/365 operating model - as such I am investigating our maintenance strategy.

One item I'm having trouble in figuring out is DBCC CHECKDB. I have seen numerous resources report that DBCC can be offloaded by performing a FULL backup, restoring that backup on a separate SQL instance, and running our CHECKDB on that restored copy. I've also found some resources that report this is not safe due to edge-case scenarios.

Is running CHECKDB safe if not run on the production database itself? If not, what parts are safe to run on a restored copy?

  • 1
    Check out this post from Paul Randal. Specifically the section Use a separate system. – Scott Hodgin - Retired Jun 12 '19 at 12:55
  • @George.palacios, we usually run DBCC CHECKDB (dbname) WITH PHYSICAL_ONLY; also in the production db often if we use a separate system to offload this which would reduce the load in production. – Biju jose Jun 12 '19 at 13:48
  • How big is the database and what version and edition you are using ? – Kin Shah Jun 12 '19 at 14:05
  • @KinShah We're on Enterprise - the size isn't relevant for this purpose as I'm not solely considering this for performance - I'm working towards a no-downtime strategy. – George.Palacios Jun 12 '19 at 14:06
  • @George.Palacios Size does matter since checkdb is a resource intensive operation. Esp. for VLDB databases - see my answer – Kin Shah Jun 12 '19 at 14:09

SQL Server expert Paul Randal discusses this option in his post CHECKDB From Every Angle: Consistency Checking Options for a VLDB

Use a separate system

This alternative is relatively simple – restore your backup (you are taking regular backups, right?) on another system and run a full DBCC CHECKDB on the restored database. This offloads the consistency checking burden from the production system and also allows you to check that your backups are valid. There are some drawbacks to this however:

  • You need to have sufficient disk space on the spare system to be able to restore the backup onto. If the production database is several TB, you need the same several TB on the spare box. This equates to a non-trivial amount of money – initial capital investment plus ongoing storage management costs. (Hopefully a future release will alleviate this – while at Microsoft I invented and patented a mechanism for consistency checking a database in a backup without restoring it.)

  • If the consistency checks find an error, you don’t know for sure that the database is corrupt on the production system. It could be a problem with the spare box that’s caused the corruption. The only way to know for sure is to run a consistency check on the production system. This is a small price to pay though, because most of the time the consistency checks on the spare system will be ok, so you know the production database was clean at the time the backup was taken.

Other than the drawbacks that Paul mentions, I'm not aware of any problems with offloading the consistency checks to another system.

I provided an answer to a question - How to conduct integrity test on SQL Server database backup file? where I described the process we use at my shop.

At my shop, we have a 'play' server that we use to automate restores of the latest FULL/DIFF of the databases we deem 'important'. We use Windows Task Scheduler to kick off a bat file which has several SQLCMD steps. After successful restores, we run full DBCC CHECKDB WITH ALL_ERRORMSGS,NO_INFOMSGS and output the result to a txt file. Then we email the output txt files to the database group for evaluation.

This process tests TWO things

  • Can I restore my backups?
  • Is the data in the backup structurally intact (DBCC)

One additional note: Corruption can happen anytime. Immediately after a good DBCC CHECKDB or successful backup, so make attempts to run DBCC consistency checks often.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.