`CHANGE_TRACKING = AUTO` fails to populate full-text index

Question

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.

Update 2019-12-ish, Resolution

Sorry this wasn't updated sooner (this actual update being in 2022-02). Lots happened since my final test and figuring everything out. I had plans for a big post, etc.

TLDR; a service pack was applied to SQL Server shortly after I had all these issues, sometime in November or December of 2019. That fixed the service stability issues I was having. I never found out exactly which patch fixed the machine, but it would have been the most recent service pack closest to that date.

Answer 1

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.

Answer 2

Have you checked the Full text log for any issues? Troubleshoot errors in a full-text population (crawl)

"(..) By default, crawl logs for a given instance (in this example, the default instance) are located in %ProgramFiles%\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\LOG folder. (..)"