I am on SQL 2014 Standard doing offline DBCC CHECKDBs (so on a box other than production). For the most part, my process is going pretty quick but for some reason I have this small DB (6gbs) and its taking hours to do the DBCC. Last time it ran it took 9 hours. It seems to freeze at different %'s completion when checking sys.dm_exec_requests. On this same server I'm doing CHECKDBs on databases in the terabytes range without a problem. I am using the following hints in the CHECKDB at this time (and, yes, eventually I'll do full instead of physical only)


What could be messed up with this one DB that is causing it to take forever?

  • Have you looked to see if the DBCC CHECKDB request is being blocked during this time? Feb 27, 2015 at 22:14
  • 1
    Do you have filtered non-clustered index on the sparse column or nonclustered indexes with computed columns ? What is the wait type associated - when it freezes -- check sys.dm_os_waiting_tasks and sys.dm_os_latch_stats?
    – Kin Shah
    Feb 27, 2015 at 22:15
  • I didn't even think to check for waits or blocks since it is literally the only thing running on there. And interestingly enough, it has a wait type of lock_m_s. It is being blocked by spid 47 (so system spid) that is doing a cmd of FT CRAWL. I'm guessing that is full text crawl? We do have full text indexes on this db (but we also have it on a lot of other DBs as well). Since all I'm doing here is restoring, checkdb, and then dropping the DB, how can I keep the FT crawl from running on my DBs? Feb 27, 2015 at 23:51
  • Couldn't you just disable full text after the restore? Feb 28, 2015 at 2:39
  • Does dbcc checkdb miss anything if I turn that off? Feb 28, 2015 at 2:47

1 Answer 1


If your CHECKDB operations are getting blocked by FT CRAWL, a reasonable solution would be to turn full-text search off for the database after it has been restored. After all, it's not as if you are using DBCC CHECKDB to validate that full-text search is doing the right thing, or that you are restoring this copy of the database in order to facilitate FT queries.

To disable full-text search for a database named foo:

  EXEC foo.dbo.sp_fulltext_database @action = 'disable';

You probably don't need the conditional since we already know FT is installed, but it's good to carry along in case this server changes or you move your restores to a different place.

Your Answer

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

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