0

I have used full text index on two columns of one table. After applying, full text index applied on all the existing rows. So I am able to search through all the records using Contains. Then I have added one record into table but full text index does not update for that row. As well when I search for that record using Contains, record does not show up in results.

Where can I check that Change_tracking is Auto/Manual/OFF?

I have another database where the same full text index on same table it is working fine. Is there anything related to SQL Server Version 2017?

Vivek Patel
  • 1,028
  • 1
  • 10
  • 22
  • Hi Vivek, can you include the table definition, the exact insert statement and the exact select statement in your question please? – TT. May 08 '19 at 09:29
  • 1
    Have you checked the [Troubleshoot Full-Text Indexing](https://learn.microsoft.com/en-us/sql/relational-databases/search/troubleshoot-full-text-indexing?view=sql-server-2017) document? The problem may be the *content* of those rows. – Panagiotis Kanavos May 08 '19 at 09:29
  • 1
    Also check the [Monitoring Full-Text Search Activity](https://learn.microsoft.com/en-us/sql/relational-databases/search/manage-and-monitor-full-text-search-for-a-server-instance?view=sql-server-2017#monitor) section. Indexing errors will appear as [XEvents](https://learn.microsoft.com/en-us/sql/relational-databases/extended-events/extended-events?view=sql-server-2017) – Panagiotis Kanavos May 08 '19 at 09:31
  • @PanagiotisKanavos, I have exported that table to another database (SQL Server 2014) and applied full text index. So with the same content it is working fine over there. So I don't think there is issue with the table content. – Vivek Patel May 08 '19 at 09:53
  • @vivekpatel and yet you have a problem. Check the logs, events, monitoring functions to see what's actually going on. If there was any problem with SQL Server 2017 people would have found out 2 years ago. Even if there is a bug, you won't be able to fix anything without actually checking logs, diagnostics and events – Panagiotis Kanavos May 08 '19 at 10:53
  • @vivekpatel in any case, [change tracking](https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-tracking-sql-server?view=sql-server-2017) is *not* an FTS feature, it's a database engine feature configured per table. You can check its status from each table's properties. It's the third category from the top. It's automatic, so talking about `auto/manual/off` is meaningless. Any application can use it to find out which rows changed. The FTS crawlers are just another client application as far as change tracking is concerned – Panagiotis Kanavos May 08 '19 at 10:57
  • @PanagiotisKanavos, where can I check the logs, events? – Vivek Patel May 08 '19 at 11:10
  • @PanagiotisKanavos, I have checked in sys.fulltext_indexes table, it shows me that last crawl time is 2019-05-07T10:55:09.033Z so I am able to search for all those record which were inserted before this time. I have inserted new records but this time does not change. – Vivek Patel May 08 '19 at 11:41
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/193030/discussion-between-vivek-patel-and-panagiotis-kanavos). – Vivek Patel May 08 '19 at 11:42

0 Answers0