SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Full-Text Search Not Updating


Full-Text Search Not Updating

Author
Message
BradGrantSky
BradGrantSky
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 579
Hi
Having issues with FTS.
Installed 2017 on Server, RTM- CU9, Os is Windows Server 2016.
The issue is that FTS is not updating.
Have checked the log but nothing there indicates an issue.
Pending changes just build up.
Restarting the FTS does not help, neither does applying track changes or incremental population.
Only repopulates after a SQL Engine restart.
This is a primary node on High Availability.
Any assistance would be appreciated.

Regards
brad
DinoRS
DinoRS
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1781 Visits: 823
so, what does the latest crawl log at the very end state why it didn't finish yet?

I have seen cases where AAGs with FTS on DBs did not finish population when failover during that time happened.
Last but not least: Did you try to force a full population for all FTS Indexes which haven't updated in a while by themselves yet without restarting SQL Server Engines?
BradGrantSky
BradGrantSky
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 579
Hi
Thanx for response.
When viewing the SQLFT logs i get 3 files around the time of the last incident.
The one is completely blank.
The other 2 have Informational information only:
2019-03-05 05:35:13.52 spid480s
Informational: Full-text Auto population completed for table or indexed view '[DatabaseName].[dbo].[TableName]' (table or indexed view ID '1870629707', database ID '7'). Number of documents processed: 1. Number of documents failed: 0. Number of documents that will be retried: 0.
There was no indication of when it stopped

regards
brad
BradGrantSky
BradGrantSky
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 579
Hi
After looking at the other logs i see the issue seems to be related to HA.
The fail over seemed to have been initiated but not completed.
It fell back to the "original" primary.
This seemed to be the reason the Full Text Search Index did not update.
As i said above, the issue was resolved only when restarting the SQL engine.
Is there another way, perhaps?

Regards
brad
DinoRS
DinoRS
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1781 Visits: 823
Yes, the other way is force a full population of the full text index, just make sure there will be no more (planned) failovers before the FTS Crawl finishes.

I once encountered a 10 TB Table almost completely covered by FTS. Once we started the full population of the FT Index it took 18 hours to complete (it was an AFA, so storage on this one for once was not the culprint of performance). During that time expect a lot of micro blockings on the Tables which are covered by FTS. Ideally this is something to be done during weekends. If FTS is quite important for you, make yourself some FTS Maintenance Jobs for at least once a month (if time constraints permit) full population and depending on your maintenance window between daily and weekly update populations.

You will not see any blocking / deadlock messages by the way from FTS full population, your application might just slow down to completely unresponsive on those tables. If you run a SQL Trace you can see those micro blockings appearing.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search