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

Improve this question
5
  • 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, 2018 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, 2018 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, 2018 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, 2018 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, 2018 at 21:02

0

Reset to default

Your Answer

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