3

I am on SQL Server 2012. I have one table with one full-text index. That index was dropped and recreated around 8am due to a change in the datatype of the indexed column from TEXT to VARCHAR(MAX). These are the relevant entries in the log:

2017-01-06 08:02:52.65 spid25s Informational: Full-text Full population initialized for table or indexed view '[Helpdesk].[dbo].[TicketPosts]' (table or indexed view ID '581577110', database ID '8'). Population sub-tasks: 4.

2017-01-06 10:56:47.98 spid9s Informational: Full-text Full population completed for table or indexed view '[Helpdesk].[dbo].[TicketPosts]' (table or indexed view ID '581577110', database ID '8'). Number of documents processed: 7016145. Number of documents failed: 0. Number of documents that will be retried: 0.

2017-01-06 11:24:42.11 spid20s Informational: Full-text Auto population initialized for table or indexed view '[Helpdesk].[dbo].[TicketPosts]' (table or indexed view ID '581577110', database ID '8'). Population sub-tasks: 1.

2017-01-06 11:24:43.12 spid9s Informational: Full-text Auto population completed for table or indexed view '[Helpdesk].[dbo].[TicketPosts]' (table or indexed view ID '581577110', database ID '8'). Number of documents processed: 0. Number of documents failed: 0. Number of documents that will be retried: 0.

Between 11am and 11:24am, the TableFulltextItemCount OBJECTPROPERTY and the TableFulltextDocsProcessed OBJECTPROPERTY were equal (and were the same as the documents processed shown in the log for the first crawl).

However, during that time the TableFulltextPopulateStatus OBJECTPROPERTY returned 1 (Full Population In Progress), sys.dm_fts_index_population.completion_type_description was 'NONE', and sys.fulltext_indexes.crawl_end_date was null.

Why did the DMVs think that the crawl was still in progress long after the log says it was done and the processed count equals the item count? What was it doing during that time?

No change was made to the data between the first and second crawl, so why did the second crawl occur?

Improve this question
2
  • Do you have your full text process configured for Change Tracking = AUTO?
    – RLF
    Commented Jan 6, 2017 at 16:56
  • @RLF: Yes, it is set to AUTO.
    – Mark Freeman
    Commented Jan 6, 2017 at 17:01

1 Answer 1

Reset to default
1

Since you have Change Tracking = AUTO configured you are telling the SQL Server to check on the changes that being made in the FULL TEXT INDEX. That means that the process is still running at a low level.

I would not be at all surprised to see some activity as the Full Text Indexing service is checking for changes.

Populate Full-Text Indexes for SQL Server 2012

A similar question was asked here back in 2013:

Full Text Search Populate Status - Processing notifications

Quoting from Frank J Garcia's answer:

There is a small overhead associated with change tracking because SQL Server maintains a table in which it tracks changes to the base table since the last population. When change tracking is used, SQL Server maintains a record of the rows in the base table or indexed view that have been modified by updates, deletes, or inserts.

Improve this answer
1
  • 2
    I don't see that either link is relevant to my questions. During the time in between the two crawls, crawl_start_date was 2017-01-06 08:02:52.65 and crawl_end_date was NULL. This seems to imply that the Full Population operation had not completed, while the log says otherwise. I've never seen "Processing notifications". During the time between the two crawls, there was no activity in the database. That table is only updated by a job that is disabled in this environment. Are we to assume that the log is correct and that the DMVs are unreliable?
    – Mark Freeman
    Commented Jan 6, 2017 at 20:56

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.