12

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:

  1. 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.)

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

  3. 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.)

3
  • Paul replied to a version of this question in the comments on his blog. He said "Don’t worry about the indexed view validation. It’s off by default from 2008 onward because it wasn’t finding problems."
    – BradC
    Aug 26, 2013 at 15:30
  • I'm working to do the same--any advice / gotcha's that you found, since you likely have already implemented this?
    – S3S
    Oct 8, 2018 at 15:44
  • 1
    @scsimon I got it to work well, see this related question for the specific strategy I used to divide the tables. I think I ultimately made a master list of all tables in all (large) databases on the entire server to divide into the daily "buckets", which gave me a much more even split than dividing up each database's list individually. Smaller databases I just did a full DBCC each day, and weren't part of the split.
    – BradC
    Oct 8, 2018 at 20:53

2 Answers 2

6

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.

7

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 :

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.