Dividing DBCC CHECKDB over multiple days

DBCC CHECKDB is vital for SQL Server databases to be 100% sure that there is no corruption. However, due to databases growing massive in size, its very hard to find a maintenance window when you claim to be 24x7 up. Over the years, SQL Server team has implemented various mechanisms that will detect most common forms of corruptions especially related to Physical corruption caused by hardware.

SQL Server 2005 and up has PAGE_VERIFY = CHECKSUM which can help you proactively detect physical corruption in database pages thereby adding a checksum to each page as it is written to the I/O system and validates the checksum as it is read from the disk.

Also, backup (full or differential) with CHECKSUM will guarantee to detect any I/O corruption caused by hardware.

Hence, from the hardware side of corruption, SQL Server does a good job of detecting it and reporting it. (Make sure to set important corruption related Alerts as well).

That being said, still logical corruption, scribbler induced errors - where in-memory pages are corrupted either by third-party code running inside the SQL Server process or by drivers or other software with sufficient privileges executing in Windows kernel mode and/or SQL Server Bugs, etc are undetectable using above methods and hence CHECKDB comes into picture.

DBCC CHECKDB performs a more thorough checks that includes checking page headers for possible corruption that are not detectable by any other means.

Any existing scripts out there?

Instead of reinventing the wheel, I would highly recommend you to take a look at Ola's SQL Server Integrity Check solution

Efficiently running DBCC CHECKDB :

You just need to be creative when you are tight in maintenance window having huge databases or high number of databases to run CHECKDB on.

After attending SQLSkills training, what I have implemented in my environment is :

• prioritize on what tables are critical to check.
• separate the tables into groups with different priorities and then run DBCC CHECKTABLE along with running DBCC CHECKALLOC and DBCC CHECKCATALOG
• Create a worker table that will store the table names with priorities. Just make sure that all high prioritie tables (which are massively big) are not in one group else your CHECKDB will not complete at all.
• You can even have a timeout column in your worker table that will orchestrate when your CHECKDB will get killed once it has passed the maintenance window
• Add how long it took per table to run DBCC CHECKTABLE, DBCC CHECKALLOC and DBCC CHECKCATALOG. So that you can get a feel on how long it is usually taking for your checks to run.
• You can even run with NOINDEX option as it will speed up the operation as it does not check the Non-Clustered Indexes on user tables. This has some advantage as it is not that critical as Data corruption since no data is lost and you can drop and recreate the Index if necessary.

Obviously, Enterprise edition can take advantage of Parallel execution of DBCC statements, but look out for MAXDOP setting as it might end up taking all your CPU. This can be hard limited by Resource Governor.

Note: If you are having SPARSE column, then your CHECKDB will be dead slow as described here.

Finally, its how to prevent database corruption by utilizing all available tool set + your faith in your database server hardware system and most importantly the value of your data.

Some excellent references :

• Is CHECKDB A Necessity?
• DBCC Checks and Terabyte-Scale Databases
• SQLU VLDB Week – Integrity Checks
• Improvements for the DBCC CHECKDB command may result in faster performance when you use the PHYSICAL_ONLY option
• SQL Server 2008 Database Checking

Are these additional checks necessary/important? (Indexed views are probably a bit more concerning to me, I don't think we are using Service Broker or FILESTREAM yet.)

You can run DBCC CHECKTABLE WITH EXTENDED_LOGICAL_CHECKS directly on the indexed views. Checking indexed views can be problematic in certain circumstances, so be prepared to investigate any false positives that result. (Paul Randal also mentions in the comments to the referenced article that false negatives are also possible, but I have no direct experience of that.)

If so, are there ways to perform these additional checks separately?

There's no support for running the Service Broker or FILESTREAM checks separately, no.

CHECKALLOC and CHECKCATALOG seem to run very quickly, even on large dbs. Any reason not to run these every day?

Not that I am aware of.

---

You might also consider running DBCC CHECKCONSTRAINTS. This check is not included in DBCC CHECKDB, regardless of any options you may specify. You may also want to think about occasionally running CHECKDB, as and when circumstances permit.