March 11, 2020 at 4:19 pm
Hello there,
I have a customer that wishes to remove a stop word from the fulltext stoplist. Unfortunately, currently we are only using the system stoplist.
Yes, I know I can create a custom stoplist, rebind it to the indexes etc.
A colleaghue came with the idea of just deleting the word from the sys.fulltext_system_stopwords.
I immediately said "no", but now I have to convice people why not take "this easy way".
Unfortunatly, I did not find the explicit sentence: modifying the system stop list is not possible.
I only find the immediate answers of making own stoplists.
Can you, my SSC colleaghues, acknowledge me, that the system stoplist is not changeable? Or is it and I am just too dump to find the right way?
Thank you very much 🙂
March 11, 2020 at 4:31 pm
Edit:
What drives me kind of crazy is the following out of books online:
Inserts or deletes a stop word in the default full-text stoplist of the current database.
Wouldn't the default stoplist be the system one? But using the "system" als stoplist name does not work...
alter fulltext stoplist default drop 'SomeWord' language german;
March 11, 2020 at 4:34 pm
Have you actually tried changing the default stoplist?
It's been a while since I've had the "pleasure" to meet FTS but I think as long as the FT Service was disabled you could edit whatever you want in FTData. In theory you should even be able to modify the system default stoplist, you just need to retrieve the name of the stoplist first.
Just be aware you need to rebuild your FT Indexes afterwards. You could aswell copy and modify the system stoplist, have a look here.
March 11, 2020 at 4:38 pm
Thank you for the reply 🙂
I did not get the name of the current stoplist yet. This statement does not give a result:
select * from sys.fulltext_stoplists
Yes, as mentioned, I am aware of going the way with own stoplists, but we are talking about a lot indexes, thus scripts, to rewrite, in a database installed several hundred times. That's why colleaghues wanted the quick way. Understandable 😉
March 13, 2020 at 7:59 am
Well, as mentioned, you can edit anything FTS related once you stop and disable the service, that includes making sure there is no FT* process running anymore. Secondly, as per second provided Link you shouldn't be surprised if you don't see the System Stoplist if you run that query against your DB because
The system stoplist is installed in the Resource database.
If you think that's programmatically less effort than simply copying and adjusting the system stoplist, be our guest.
From my experience with FTS I would expect any DB on a server with a modified System Stoplist to initiate an FTS Index Rebuild on any DB which is FT enabled and is not explicitly using a different FTS Catalog.
If the tables and indexes using FTS are large , you can expect up to several days of micro blockings on such FT Table, to give you a relation to this: I once had to do this to a 10 TB Table and despite the storage being all Flash and exclusively to SQL Server provisioned, it took 3,5 days for FTS to stop these micro blockings and start being accessible again. And no, you cannot stop that from happening and killing the FTS process will just spawn another one.
And if you manage to access the table while he's doing the rebuild, he might decide to start from scratch.
Good luck with whatever call you're going to make on this, you'll definitely need it.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply