Skip to main content
Bumped by Community user
Bumped by Community user
Bumped by Community user
Bumped by Community user
Bumped by Community user
Bumped by Community user
Bumped by Community user
Bumped by Community user
Bumped by Community user
Bumped by Community user
Bumped by Community user
Bumped by Community user
Bumped by Community user
Bumped by Community user
Bumped by Community user
Bumped by Community user
Bumped by Community user
Clarified Question Title
Link
Calgary Coder
  • 315
  • 1
  • 3
  • 11

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

Source Link
Calgary Coder
  • 315
  • 1
  • 3
  • 11

SQL-Server Full-Text Index 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?