I'm working on implementing Paul Randal's method of manually spreading DBCC CHECKDB over several days from his excellent article CHECKDB From Every Angle: Consistency Checking Options for a VLDB. In short, the strategy consists of:
- Divide the tables in the database equally between 7 buckets (using number of pages)
- Run a DBCC CHECKALLOC twice a week
- Run a DBCC CHECKCATALOG once a week
- Run a DBCC CHECKTABLE on one bucket each day of the week
I've got a separate StackOverflow question regarding a good algorithm to divide the tables into buckets (chime in there if you have a working TSQL script), but this question is about making sure I'm checking the right things. The Books Online documentation for CHECKDB says that in addition to doing the 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 there ways to perform these additional checks separately? Should I even worry about it? (Indexed views are probably a more immediate concern to me than the other two)
Any significant alterations I need to make to my strategy if I want to implement this across SQL2000, 2005, and 2008 databases?
CHECKALLOC and CHECKCATALOG seem to run pretty quickly. Any reason not to run these 2 checks every day?