Skip to main content
2 of 2
Clarified Question Title
Calgary Coder
  • 315
  • 1
  • 3
  • 11

How to find cause of SQL-Server Full-Text Index Auto-Population Error?

I have recently added a few full text indices to a database and AUTO populating is not working as I would have expected.


Sample Table Definition

CREATE TABLE [wrk].[WorkOrder] (
    [id]            UNIQUEIDENTIFIER  NOT NULL,
    ...
    [customer]      VARCHAR(250)      NOT NULL,
    ...
    CONSTRAINT [PK_WorkOrder] PRIMARY KEY ([id]),
);

Sample Index Definition

CREATE FULLTEXT INDEX ON [wrk].[WorkOrder] ([customer] LANGUAGE 1033) 
KEY INDEX [PK_WorkOrder] ON [DefaultCatalog] WITH STOPLIST = OFF, CHANGE_TRACKING AUTO; 

Sample Data

A6A1CEE3-4AA8-41EF-B681-E24B8B5DA03C    AltaGas Extraction and Transmission LP
F0B757FB-0A48-4AB0-9411-72AAF64E677B    Caltex Resources Ltd.
D4339B95-9AEA-4C79-ACBE-9194ED8FA1D5    Rock Energy Inc.

Other Details

  • SQL Server 2008 R2 Standard Edition
  • SQL Server & SQL Full-text Daemon Launcher running as same privileged account.
  • Windows Server 2008 R2 Standard 64 bit VM
  • Indices are built using the DefaultCatalog
  • Indices are using English language.
  • Catalog is accent sensitive.

If I look at the SQLFT logs, I am seeing that new entries typically go through the following flow:

...
2015-06-05 18:18:05.99 Informational: Full-text Auto population initialized for table or indexed view '[wrk].[WorkOrder]' (table or indexed view ID '844074293', database ID '7'). Population sub-tasks: 1.
2015-06-05 18:18:09.02 Error '0x80070005' occurred during full-text index population for table or indexed view '[wrk].[WorkOrder]' (table or indexed view ID '844074293', database ID '7'), full-text key value 'F0B757FB-0A48-4AB0-9411-72AAF64E677B'. Attempt will be made to reindex it.
2015-06-05 18:18:09.02 Informational: Full-text Auto population completed for table or indexed view '[wrk].[WorkOrder]' (table or indexed view ID '844074293', database ID '7'). Number of documents processed: 1. Number of documents failed: 1. Number of documents that will be retried: 1.
2015-06-05 18:18:10.01 A full-text retry pass of Auto population started for table or indexed view '[wrk].[WorkOrder]'. Table or indexed view ID is '844074293'. Database ID is '7'. 
...

If I search for the record associated with F0B757FB-0A48-4AB0-9411-72AAF64E677B no results are returned. If I manually rebuild the index, I will be able to search the data as expected.


How do I find out why the auto-population failed (i.e., reason for retry pass failure)? Do I have something configured incorrectly to have auto-population work as expected?

Calgary Coder
  • 315
  • 1
  • 3
  • 11