1

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?

  • Ensure that the SQL Server service account that you use to run the SQL Server, SQL Server Agent, and SQL Full-text Filter Daemon Launcher windows service have the proper rights to perform the full-text index population and also the access to write file into the MSSQL log folder. – Kin Shah Jun 8 '15 at 16:35
  • I would be interested in knowing how to determine the cause of the failure. If this were the cause, would there be an error message logged somewhere that I can verify (the account should have access)? – Calgary Coder Jun 8 '15 at 19:16
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.