sql-serverfull-text-searchfulltext-index

Fulltext index crawls stuck at has_crawl_completed 0


On a ~100GB database with hundreds of tables I've created fulltext catalogs per-table. I set all the change tracking to OFF just to begin. After over a day, most of the indexes have finished crawling, but there are some which haven't finished. In sys.fulltext_indexes the has_crawl_completed for these indexes still show 0.

Referring to the answer from How can I know when SQL Full Text Index Population is finished?, I check the PopulateStatus of the catalogs corresponding to these not completed indexes. There is one catalog that is in the Full Population In Progress status and a few catalogs that are in the Idle status.

Is there anything I can do to monitor the catalog which is in Full Population In Progress? What does a catalog that's Idle but has incomplete crawls mean -- what is the purpose of those indexes if the catalog for them is already Idle?


Solution

  • I was able to retry crawling on the stuck indexes by running this for every index that had a 0 for has_crawl_completed:

    ALTER FULLTEXT CATALOG <catalog-name> REBUILD;
    ALTER FULLTEXT INDEX ON <table-name> START FULL POPULATION;
    

    and the retries completed eventually.