DBCC CHECKDB is easy with small databases – just run it every day. But as your database grows, this quickly stops becoming an option, and you need to start cutting corners. Learn how to cut them safely by running DBCC CHECKDB at a realistic frequency, discover how to run it faster, and see which SQL Server options like backup checksums and page checksums can give you CHECKDB-like protection without the long maintenance windows.

8 Comments. Leave new

  • Sandeep Pawar
    June 12, 2020 9:40 am

    Excellent !! learned some fantastic things about CHECKDB and data corruption. Thank you Brent.

    Reply
  • Kapil Bhasin
    August 3, 2020 3:46 pm

    Very helpful, Thanks Brent! Quick question, for SQL server prior to 2014 and 2014SP1 as well I believe all CHECKDB operations are run with default of MAXDOP1. However post 2014 SP2 and above if one simply runs CHECKDB without any MAXDOP leaving default what is happening? Will it take my server level MAXDOP setting say 8 and what you said will flush the cached plans? or is that only applicable when I run CHECKDB WITH MAXDOP option? Sorry for bombarding with those questions but just confused on this part. Thank you!

    Reply
  • Kapil Bhasin
    August 3, 2020 3:53 pm

    Sorry I meant in older versions like SQL2012 CHECKDB running serially. I believe that parameter to give MAXDOP on CHECKDB started post 2014SP2. So my worry is when that plan will be flushed from cache, only when running DBCC CHECKDB with some MAXDOP parameter specified or every time when my DBCC CHECKDB runs on latest SQL servers as you said they have been parallel for a while? Thanks

    Reply
    • I’m still not agreeing with you here. Let’s do this: go find the material that supports your claim that CHECKDB ran serially in those versions, and then run tests to get the answer to your question. (I don’t think you’re asking the right question, and I don’t think you have the right background info, and I want to respect your time by doing as little back-and-forth as possible.)

      Reply
  • Kapil Bhasin
    August 3, 2020 4:14 pm

    Thanks, My bad! Totally got confused with the slide where it says Is Check DB using parallelism and then read somewhere else something crappy. I thought running DBCC CHECKDB when specifying a MAXDOP parameter will flush the plan cache. But I think you were just referring when we change MAXDOP at instance level will flush the plan cache.

    Reply
  • Biniyam bizuye
    December 1, 2020 8:16 pm

    Hello Brent/Others,

    is there any way to be notified if the checkdb finds corruption in the check? I created an agent job and corrupted a database on purpose and when i run the job it completes successfully and logs the errors, but at no point does it send a notification that it founds errors. any suggestions/links would be appreciated.
    Thank you!

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu