Importance of how you run consistency checks

Back in 2009 I ran a survey about methods of running consistency checks. I recently re-ran the survey to get a better idea of the prevailing methodologies – hoping that more people were offloading consistency checks in concert with testing backups.

The results are very interesting.

2009 survey results:

2012 survey results:

The results are almost *exactly* the same. This is quite surprising to me as I expected more people to be offloading the consistency checks because of resource constraints on the production systems. However, it does show that my previous survey was statistically accurate, even with only 67 responses.

The Other results for this year’s survey don’t really change the distribution of answers, but add a few percent to the first answer. They are:

  • 11 x DBCC with NO_INFOMSGS, ALL_ERRORMSGS on production database.
  • 8 x Combination of 1 and 2.
  • 7 x Run DBCC CHECKDB with NO_INFOMSGS on the production database.
  • 5 x Combination of 1 and 3. where maintenance windows permit DBCC CHECKDB with no options on the production server, otherwise on a restored backup on another server.
  • 4 x It depends.
  • 3 x A mix of PHYSICAL_ONLY and DATA_PURITY depending on server and day of week.
  • 3 x Run DBCC CHECKDB with no options after backup and restore to a test environment.
  • 2 x DBCC CHECKDB WITH DATA_PURITY on production database.
  • Combination of option 1, 2 and.
  • Most are option 1, a few option 2, and we are considering CHECKTABLE for our largest instance
  • Mostly CHECKDB with PHYSICAL_ONLY; one instance with CHECKTABLE over multiple days.
  • What is DBCC CHECKDB

Any of the answers where DBCC CHECKDB is being run on a continually updating copy of a database (e.g. through a database mirror, SAN mirror, log-shipping or Availability Group secondary) are incorrect. This method tells you nothing about the state of the main database on the production system as two different I/O subsystems are involved. I’ve discussed this many times before so I won’t labor the point, but you either have to run the consistency checks on the production database, or on a restored backup of it, or you’re not testing the production database. Nothing else is good enough.

For the people using BACKUP … WITH CHECKSUM instead of doing regular consistency checks, you’re running the risk of bad memory chips corrupting your database – see A SQL Server DBA myth a day: (27/30) use BACKUP WITH CHECKSUM to replace DBCC CHECKDB.

Thanks to all who participated in the survey!

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.