1

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.

0

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. (..)"

  • 2
    The log contents are in my question. I will update the question to indicate where the log message came from. – ps2goat Oct 8 at 17:10
0

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.

  • 1
    I gave all the information I could find, based on other info sources I Googled. Error '0x80004005' from the full text log file seems like it may be an access issue, but like I said in the question, I created a new user for the Full Text service, assigned it, restarted the services, and verified effective permissions on all the filters and wordbreakers installed (all are default). My next step is probably to remove individual filters, but I didn't want to do that since this is a default install. – ps2goat Oct 8 at 15:24
  • 1
    I did verify named pipes was enabled. And on a second machine I started verifying, there are two instances installed; each instance uses its own named pipe that is named after the instance. (Don't get hung up on that situation-- I'm trying to prove this out on my test server first, with only a default instance installed. For verification purposes, I am trying these steps on a second server and having the same results.) – ps2goat Oct 8 at 15:26
  • 1
    I get two events in Event Viewer: 30090, meaning the service started, and 30089 (detailed in the first update to my question), meaning the service stopped unexpectedly. These two go hand-in-hand; when 30089 is logged, 30090 is also logged because the FDHost process starts up a new instance of the service. – ps2goat Oct 8 at 15:28
  • 1
    Any other suggestions for where to look for additional logs, settings to tweak, etc., would be appreciated. – ps2goat Oct 8 at 15:29
  • 1
    Yes, it's at level 140. Note that I double checked the SQL Server edition, and it is the Developer Edition (I'm in a testing environment right now). – ps2goat Oct 8 at 18:14

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.