4

I have a 2016 SQL Server freshly built - 13.0.5149.0 - OS 2016 (Microsoft Windows NT 6.3 (14393)). The environment is VMWare. No SQL Server HA configuration.

It has a problem where CheckDB "stops" with a wait type of CHECK_TABLES_THREAD_BARRIER. It has happened twice on 2 different databases. I don't see much listed on online resources for what to do with this wait. Microsoft lists "TBD" on information. This is a non production proof of concept environment for an upgrade.

Can anyone provide any insight or next steps?

Improve this question
  • So if I understand correctly this wait type occurs when rollback happens of checkdb after it fails ?. How much time does the rollback takes. You should focus on why checkdb terminates abruptly that could tell reason for the wait type. – Shanky Jan 3 '19 at 15:22
  • It never rolls back. I've restarted services to clear it. The checkdb task is currently at 12% complete. – Dan White Jan 3 '19 at 15:29
  • 1
    “Check” and “threads” leads me to suggest maybe try checkdb with maxdop... – Aaron Bertrand Jan 3 '19 at 16:26
  • Just for testing purposes, I would see if the smaller checks, DBCC CHECKALLOC, DBCC CHECKCATALOG are able to run. Possibly also a DBCC CHECKTABLE over the tables / indexed views. DBCC CHECKTABLE on indexed views with extended logical checks seems to have had bugs in the past however : dba.stackexchange.com/questions/47295/… – Randi Vertongen Jan 3 '19 at 19:56
  • Thanks Aaron, a change in maxdop is allowing us to get around the issue. I would still like to learn more about what that wait type actually means but looks like Microsoft it keeping it a secret for now. Thank you for your time! – Dan White Jan 4 '19 at 14:03
2

The wait types are sometimes sort of self-documenting, in this case I would piece together that something is causing CHECKDB to use too many threads. One way to attempt to minimize or eliminate this is to restrict CHECKDB to using a single core with the MAXDOP option:

DBCC CHECKDB ('database') WITH MAXDOP = 1;

I have other ideas for minimizing impact of CHECKDB here (granted it was written long before the MAXDOP option was added, so I need to update the post):

If you want an official answer from Microsoft, I suspect you'll have to open a support case. It may be that there is a bug leading to this symptom, in which case the support call will be free, but I wouldn't go through that just to get the explanation of what CHECK_TABLES_THREAD_BARRIER means, especially when you know you have a viable workaround.

Improve this answer
3

Although I don't have any info on this yet in my waits library (will be fixed soon), you're likely hitting this hang when CHECKDB has hit an error state and it waiting for the various threads to finish running.

This is a rare, known issue that Microsoft is investigating but as yet has not identified or fixed.

Thanks

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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