4

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)

WITH NO_INFOMSGS, ALL_ERRORMSGS, PHYSICAL_ONLY, TABLOCK

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? – Thomas Stringer Feb 27 '15 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 Feb 27 '15 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? – Chris Woods Feb 27 '15 at 23:51
  • Couldn't you just disable full text after the restore? – Aaron Bertrand Feb 28 '15 at 2:39
  • Does dbcc checkdb miss anything if I turn that off? – Chris Woods Feb 28 '15 at 2:47
3

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:

IF FULLTEXTSERVICEPROPERTY('IsFullTextInstalled') = 1
BEGIN
  EXEC foo.dbo.sp_fulltext_database @action = 'disable';
END

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, privacy policy and cookie policy

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