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
CHANGE_TRACKING = AUTO,
STOPLIST = OFF
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.)
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.
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
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.