Integrity checks on large dbs (700GB)

Question

What's the best strategy for integrity checks on large dbs? DBCC CHECKDB (DB Name) would probably take over 24 hours and the largest table, being 532GB, would probably take over 17 hours.

Answer 1

Based on your question it seems like this actually has not been run yet because you are using "probably".

However, there are two branches for this question:

• Run the Integrity checks on a backup of the database.
  • Take a full database backup of the prod server
  • Restore the database on another server
  • Run consistency checks on the restored database
• Break up the DBCC CHECKDB over multiple days.
  • Aa mentioned in Consistency Checking Options for a VLDB

It is NOT AN OPTION to run Consistency checks on a mirror database or on an availability group replica as a way of checking the principal or primary. Since these will be running on different I/O systems and corruptions do not propagate between servers. This will need to be run IN ADDITION.

---

Things to make CHECK DBCC run faster.

---

• Use the physical only option trace flag 2562, and trace flag 2549
• reduce memory grant

• Disable non-clustered indexes on computed columns ahead of time – There is an issue with the DBCC_OBJECT_METADATA latch that it requires an exclusive lock to run.

• TEMPDB performance will greatly impact the performance of your CHECKDB If you want to change the amount of cores your DBCC command, you can either use:

  • TRACE FLAG 2528
  • SET MAXDOP