2

We have a SQL Server 2014 with 140 user databases, each of which have approx. 180 tables. A maintenance plan was scheduled for once a week but it was discovered that the Check Database Integrity (DBCC CheckDB) job was taking too long. The schedule was disabled, the job stopped and the process killed.

However, we are now seeing DBCC TABLE CHECK command running on multiple threads. The MAXDOP configuration setting has been reduced to give other processes a chance, but the table check is still using as many threads as it can get hold of.

The question is: How to kill this process?

BTW - the server has been rebooted(twice).

  • What is output of select @@version and select requested_memory_kb,granted_memory_kb from sys.dm_exec_query_memory_grants where session_id=xx. Where session_id is session ID of checkdb running. – Shanky Oct 4 '18 at 14:05
  • Can you run sp_who2? I bet the SPID it's running under shows as killed / rolledback. If not, you can kill those SPIDS, but I would not advise that. If you do, it will fall back onto a single CPU to rollback, taking much longer and wasting the time you have already spent. If you did kill it and it is in rollback, you will have to wait for it as it regresses on a single CPU. – Shaulinator Oct 4 '18 at 14:05
  • @Shanky thanks for you response. Output from both queries - Microsoft SQL Server 2014 - 12.0.4100.1 (X64) Apr 20 2015 17:29:27 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor) requested_memory_kb granted_memory_kb 5177800 5177800 – jock Oct 4 '18 at 16:41
  • @Shaulinator thanks for your response. Unfortunately the original CheckDb process was killed and it has left the CheckTable remnants running - as part of the rollback process I presume. I've already had to reduce the MAXDOP to allow other processes some processor time. Your advice of leaving it to run seems to be the general consensus. – jock Oct 4 '18 at 16:49
  • Is there any way to determine which table DBCC CHECKTABLE is currently running on? I can find the current database, but not the table. – jock Oct 4 '18 at 21:02

Your Answer

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

Browse other questions tagged or ask your own question.