CODEWITHSUNDEEP
sql-serversql-server-2016full-text-indexingfreetext
Bob
Bob

Reputation: 65

SQL Server 2016 Full-Text Index suddenly stops working

We are successfully using Full-Text Indexing, but multiple times a week it suddenly stops working.

Going to the affected table and disabling the Full-Text Index, then enabling it again brings it back to life. But that's no good in a production environment!

I've checked the event log and there doesn't seem to be any error from SQL Server that might be an indication as to the cause.

Any ideas? Thanks for any input!

Upvotes: 2

Views: 1833

Answers (1)

Bernd R
Bernd R

Reputation: 21

I tested the sql statement of @RaduGheorghiu, the result for the table in question is completely empty, but it delivers results for a table where fulltext is operational.

As @BobFindlay said, the only way to get the results back is to de- and reactivate the Full-Text Index, refill won't work. I also found the reason to this behaviour in another topic: SMSS is responsible for destroying the catalog When you use SMSS to add a column, SMSS will drop and recreate this and every related table (including Full-Text Index).

So you have two options if you want to prevent that from happening:

Never use SMSS for altering anything, use DDL commands instead. Or manually disable and enable Full-Text Index every time you made any change to your table (Or any related table).

Upvotes: 2

Related Questions