2

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!

TT.
  • 15,774
  • 6
  • 47
  • 88
Bob
  • 65
  • 7
  • 3
    What do you mean by *stopping working.* ? Is it throwing any error or does not find any results or what ? – Pரதீப் Dec 07 '16 at 14:10
  • As Prdp says, what's the error message. Also, do you have any index maintenance running? – Rich Benner Dec 07 '16 at 14:30
  • it doesn't give an error, it just stops returning results – Bob Dec 07 '16 at 15:16
  • You mean SQL Server hangs by "not working"? – TT. Dec 07 '16 at 16:43
  • What does `select * from sys.dm_fts_index_keywords( DB_ID('database_name'), OBJECT_ID('table_name') ) ` return? – Radu Gheorghiu Dec 07 '16 at 16:45
  • TT: no. it just stops returning results when we perform a "freetext" search. The same search works after we toggle the full-text index. – Bob Dec 07 '16 at 16:47
  • Radu: we had to re-enable it, so it's working at the moment and it returns 33000 rows. When it breaks next, I'll do it again. – Bob Dec 07 '16 at 16:49
  • Ok @BobFindlay, but next time please use the `@` handle before my name so I know when you post an update, as otherwise I'll most likely forget checking on this question. – Radu Gheorghiu Dec 08 '16 at 07:44

1 Answers1

2

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

Daniel F
  • 13,620
  • 2
  • 29
  • 55
Bernd R
  • 21
  • 3