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;
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.
- 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
- Indices are using
- 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?