Dividing DBCC CHECKDB over multiple days I'm working on implementing Paul Randal's method of manually spreading DBCC CHECKDB over several days for very large databases, which basically consists of:


Dividing the tables in the database roughly equally between 7 buckets 
Running a DBCC CHECKALLOC twice a week
Running a DBCC CHECKCATALOG once a week
Running a DBCC CHECKTABLE on one bucket each day of the week


Has anyone used this technique? Any existing scripts out there?

I'm concerned this may not actually cover everything that CHECKDB does; the Books Online documentation for CHECKDB says that in addition to CHECKALLOC, CHECKCATALOG and CHECKTABLE, it also:


Validates the contents of every indexed view in the database.
Validates link-level consistency between table metadata and file system directories and files when storing varbinary(max) data in the file system using FILESTREAM. (SQL 2008 only)
Validates the Service Broker data in the database.


So here are my questions:


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.)
If so, are there ways to perform these additional checks separately? 
CHECKALLOC and CHECKCATALOG seem to run very quickly, even on large dbs. Any reason not to run these every day?


(Note: this will be a standard routine for thousands of existing databases across hundreds of servers, or at least every database over a certain size. This means that options like restructuring all databases to use CHECKFILEGROUP aren't really practical for us.)
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