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

Question

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?

Answer 1

Using the Microsoft Error Code Lookup Tool shows Error 0x80070005 is "Access Denied". This is the output from the tool:

# for hex 0x80070005 / decimal -2147024891 :
  COR_E_UNAUTHORIZEDACCESS                                      corerror.h
# MessageText:
# Access is denied.
  DIERR_OTHERAPPHASPRIO                                         dinput.h
  DIERR_READONLY                                                dinput.h
  DIERR_HANDLEEXISTS                                            dinput.h
  DSERR_ACCESSDENIED                                            dsound.h
  ecAccessDenied                                                ec.h
  ecPropSecurityViolation                                       ec.h
  MAPI_E_NO_ACCESS                                              mapicode.h
  STIERR_READONLY                                               stierr.h
  STIERR_NOTINITIALIZED                                         stierr.h
  E_ACCESSDENIED                                                winerror.h
# General access denied error
# 11 matches found for "0x80070005"

Ensure the service accounts used by SQL Server, SQL Server Agent, and SQL Full-text Filter Daemon Launcher Windows Services have the correct access. Microsoft Docs has some great details about how these services interact.