Click here to Skip to main content
15,943,008 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
I have a table with a full-text index that is essentially a denormalized view of several tables that is automatically updated when any of the base tables are updated via triggers. This table is primarily used for a nightly operation, but I would like to use it for more real-time things if I could only get the population figured out.

When setting up the full-text index, I set 'track changes' to 'Automatic' as I didn't want have to fully populate the index every night. However, it doesn't seem to incorporate newly added records for some time, if at all. I can only get new records to return in FREETEXTTABLE query by re-building the index.

I tried setting up a population schedule twice a day, but that doesn't seem to do anything. I am testing it by looking at newly inserted records in the base tables and searching for them in a FREETEXTTABLE query, expecting the correct ID to show up in the search results.

When researching online, I kept seeing 'populate in background' as a potential solution, but I never saw anything about how to actually enable this.

Any ideas?

1 solution

Most likely the background population you saw is another term for tracking changes.

If you add large amount of data in your nightly operations, I would suggest that you drop the index before nightly operations, do the operations and then create the index again (so not using rebuild). When creating the index again, put the change tracking to auto so the index should keep track on the day time operations.
Share this answer
Zach Swanson 17-Aug-11 10:27am    
Thanks for your help Mika. The data operations are performed during the day using a web interface. When making changes, updates to the base tables are automatically copied to the fulltext table (denormalized view) via triggers updating only the affected rows and fields.

How is dropping and re-creating the index different from a full re-population? Ideally, I am wanting the index to be up-to-date within 4 hours of changes made.
Wendelius 17-Aug-11 12:26pm    
From your post I understood that you 'massively' updating the data at night time. If that's the case then dropping the index and creating it after the data operations would have 2 effects:
- the speed of data load would be most likely higher (no index maintenance)
- index would be up-to-date after creating it with 'fresh' data

I've seen some situations where change tracking is ignoring some changes which eventually causes missing entries. This has happened if the table containing the index is modified very fast. That's another reason why I suggested not having the index when data is loaded. But as said these apply only if you have a 'load period' for the table

If you allow the index to be few hours late, one possibility is that you don't use background population but create a job under SQL Server agent. In this job you either populate the index manually (ALTER FULLTEXT INDEX...) or rebuild it. You can schedule this job to execute for example every 4 hours.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900