Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
FYI, I'm reposting this from a very old question on ServerFault that never got any good answers: serverfault.com/questions/200653/… –  BradC Aug 19 '13 at 20:23
1  
I've never tried Paul's method before but I can tell you that he was one of the developers for DBCC CHECKDB so if he says it covers everything I would feel fairly confident in it. You can always email him and ask. He is very good about responding to emails. –  Kenneth Fisher Aug 19 '13 at 20:53
    
Yes, I'm familiar with Paul's reputation, I've asked a version of this question in a comment on Paul's blog, which is currently in moderation. –  BradC Aug 19 '13 at 22:43
    
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 '13 at 15:30

2 Answers 2

up vote 3 down vote accepted

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.

share|improve this answer

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 :

share|improve this answer
    
Thanks for the detailed reply and the reference links. I am already familiar with most of the issues you discuss, but it is a good write-up. (Ironically, most of the links you supply point back to Paul's original article I linked in my question.) Ola's script, though, appears to be very similar to what we already developed in-house, and doesn't support the kind of "table grouping" my question is asking about. Also, any comment on my concern that CHECKDB appears to "validate the contents of indexed views", but that CHECKTABLE does not? –  BradC Aug 19 '13 at 22:57
1  
@BradC CHECKTABLE --> Checks the integrity of all the pages and structures that make up the table or indexed view (technet.microsoft.com/en-us/library/ms174338.aspx) –  Kin Aug 19 '13 at 23:48
    
Ah, so run CHECKTABLE explicitly on each indexed view. That makes sense. Thanks. –  BradC Aug 21 '13 at 14:07
    
@BradC IF you feel that my answer has answered your question, please upvote/mark as answer, so it can help future visitors. –  Kin Aug 21 '13 at 20:20
1  
Yeah, I know how SE sites work, thanks. But no, I won't be marking your answer as accepted. It was basically an extended primer of stuff about DBCC I already knew, but didn't answer my question. It might be useful to others, so I did give it an upvote. The only thing that directly answered my question was your comment above that CHECKTABLE can be used against an indexed view. I gave that comment an upvote. –  BradC Aug 21 '13 at 20:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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