SQL Server – How to Resolve Long Running DBCC CHECKDB

dbcc-checkdbsql server

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?

Best Answer

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.

Related Question