Sql-server – `CHANGE_TRACKING = AUTO` fails to populate full-text index

errorsfull-text-searchsql serversql-server-2017

I have created full-text indexes on tables with the following options (simplified):

CREATE FULLTEXT INDEX ON [schema].[table]  
(  
    ColumnA Language 'English',
    ColumnB Language 'English'
)  
KEY INDEX pk_table ON database_ft_catalog 
WITH
    CHANGE_TRACKING = AUTO,
    STOPLIST = OFF
GO

This works, and rows are searchable after the index builds.

Select Top 100 * from [schema].[table]
  where contains(*, '"searchTerm*"');

My problem is that rows are not automatically populated into the full-text index like they should be.

At one point, I saw in the logs that the full-text daemons died, so I restarted those using sp_fulltext_service 'restart_all_fdhosts'. (sys.fulltext_indexes showed that a full population was still underway.)

This worked for a while, but now new rows are once again not being indexed. This is my latest error message from the Full Text Log:

A full-text retry pass of Auto population started for table or indexed view

Error '0x80004005' occurred during full-text index population for table or indexed view

Informational: Full-text retry pass of Auto population completed for table or indexed view '[db].[schema].[table]'... Number of retry documents processed: 1. Number of documents failed: 1.

Any information on how to fix this would be greatly appreciated. The rows are failing four times, then are ignored after that, i.e., they are no longer tracked and will not make it into the full-text index until I rebuild it.

FYI, I did try to increase the ism_size as listed in this article. However, that did not fix the issue. (I know that's an old version, but it was worth a shot.)

UPDATE:

I found some additional posts today that said to look in Event Viewer. I'm getting Event ID 30089:

The fulltext filter daemon host (FDHost) process has stopped abnormally. This can occur if an incorrectly configured or malfunctioning linguistic component, such as a wordbreaker, stemmer or filter has caused an irrecoverable error during full-text indexing or query processing. The process will be restarted automatically.

The Windows Service (SQL Full-text Filter Daemon Launcher – FDHost) is restarting anywhere between 1 and 30 seconds. The full text service is using the default SQL Server account NT Service\MSSQLFDLauncher, which I made sure has READ & EXECUTE access to the C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Binn folder and all child objects, which is where sp_help_fulltext_system_components 'wordbreaker' reported the wordbreaker packages were all located.

Still no change.

Update 2019-10-07

It turns out these are using EC2 in AWS, with Amazon's Windows 2016 image. We installed SQL Server Standard 2017 ourselves, though. It looks like the AMI is Windows_Server-2016-English-Full-Base-2019.07.12

Update 2019-10-08

As I'm in a testing environment, I'm using SQL Server Edition. The database is at compatibility level 140 (2017) and the SQL Server instance and databases are all fresh installs, no upgrades.

The database file is set to unrestricted max size, logs file is set to max of 2TB, both grow by 64MB. The database file is currently at 1.288 GB and the logs are 776 MB. There is enough room on the configured disk for it to grow another 20-40 GB.

One tweak I could make for performance issues is put the full text indexes on a separate disk, but I'm currently the only one using this database. I just want the process to complete without errors and be able to add newly inserted or modified records to the index.

Note that my primary key is an identity column per performance recommendations, and all of my columns are built-in data types (varchar(255) seems to be the max), and that these tables have system versioning enabled, if that affects anything.

Best Answer

You said that the SQL Full-text Filter Daemon Launcher - FDHost is restarting. Do you have any error in the Event Viewer that would suggest it doesn't have the appropriate rights to run?

You could check the account and password and check that the permissions are sufficient on the computer it runs.

You should check that the named pipes are enabled.

For example, the documentation states that :

If named pipes functionality has been disabled on the local computer, or if SQL Server has been configured to use a named pipe other than the default named pipe, the SQL Full-text Filter Daemon Launcher service might not start.

It also says that you can have problems if another instance of the same named pipe is already running.

If you can provide additional error logs, maybe we could help you futher.

I read a post here that says that this error got resolved once the SQL Service, Agent Service and Full Text Service all run on the same account. Maybe it's something worth trying.

You can try to query sys.dm_fts_fdhosts or another of the DMV views related to fulltext indexes. Maybe you can find additionnal information on what's going on there.

Related Solutions

Sql-server – Full Text Catalog not populating

You might try scripting it out to see if you get a different behaviour. This simple example runs in under a minute on my local SQL 2005 instance:

USE master
GO

SET NOCOUNT ON
GO

IF EXISTS ( SELECT * FROM sys.databases WHERE name = 'fullTextDemo3' )
BEGIN
    ALTER DATABASE fullTextDemo3 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE fullTextDemo3
END
GO

IF NOT EXISTS ( SELECT * FROM sys.databases WHERE name = 'fullTextDemo3' )
CREATE DATABASE [fullTextDemo3]
GO

ALTER DATABASE fullTextDemo3 SET RECOVERY SIMPLE
GO

USE fullTextDemo3
GO

CREATE TABLE dbo.nearnessTest (
    rowId           INT IDENTITY,
    keywords        VARCHAR(MAX) NOT NULL,
    notes           VARCHAR(100) NOT NULL,

    CONSTRAINT PK_nearnessTest PRIMARY KEY ( rowId )
)
GO


INSERT dbo.nearnessTest
--SELECT TOP 270000 m1.[text], ''
SELECT m1.[text], ''
FROM sys.messages m1
--  CROSS JOIN sys.messages m2
WHERE m1.language_id = 1033
  --AND m2.language_id = 1033
GO

-- Create the catalog
IF NOT EXISTS ( SELECT * FROM sys.fulltext_catalogs WHERE name = N'ftc_List3' ) 
CREATE FULLTEXT CATALOG ftc_List3
GO

-- Create the full-text index
CREATE FULLTEXT INDEX ON dbo.nearnessTest (keywords) KEY INDEX PK_nearnessTest ON ftc_List3 WITH CHANGE_TRACKING MANUAL -- CHANGE_TRACKING OFF, NO POPULATION
GO

SELECT 'before' ft, * FROM sys.fulltext_indexes
GO

DECLARE @i INT 
SET @i = 0

WHILE EXISTS ( SELECT * FROM sys.fulltext_indexes WHERE has_crawl_completed = 0 )
BEGIN

        SELECT outstanding_batch_count, *
        FROM sys.dm_fts_index_population
        WHERE database_id = DB_ID()

        --SELECT *
        --FROM sys.dm_fts_outstanding_batches
        --WHERE database_id = DB_ID()

    WAITFOR DELAY '00:00:05'

    SET @i = @i + 1
    IF @i > 60 BEGIN RAISERROR( 'Too many loops!', 16, 1 ) BREAK END

END

SELECT 'after' ft, * FROM sys.fulltext_indexes
GO

SELECT * 
FROM dbo.nearnessTest 
WHERE CONTAINS( keywords, 'error' )

What happens when you issue an explicit start for the index population, eg

ALTER FULLTEXT INDEX ON dbo.yourTable START FULL POPULATION;

Run these summary functions

SELECT FULLTEXTSERVICEPROPERTY ( 'IsFulltextInstalled' ) IsFulltextInstalled
SELECT DATABASEPROPERTY('fullTextDemo3','IsFulltextEnabled') IsFulltextEnabled
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'AccentSensitivity ') AS AccentSensitivity 
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'IndexSize ') AS IndexSize 
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'ItemCount ') AS ItemCount 
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'LogSize ') AS LogSize 
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'MergeStatus ') AS MergeStatus 
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'PopulateCompletionAge ') AS PopulateCompletionAge 
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'PopulateStatus ') AS PopulateStatus 
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'UniqueKeyCount ') AS UniqueKeyCount 
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'ImportStatus ') AS ImportStatus

Sql-server – How to find cause of SQL-Server Full-Text Index Auto-Population Error

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.