August 4, 2013 at 8:34 pm
I have a full text catalog containing a number of tables/views. Tables/views in the full text catalog have "Do not track changes" and "Manual" selected for "Track changes". A nightly job was created that executes the following command:
ALTER FULLTEXT CATALOG [catalog_name] REORGANIZE
From a maintenance perspective, should any of the above be changed to ensure that catalogs are populated at least once a day? Should I be manually executing the following command on a daily basis for all tables in the fulltext catalog [catalog_name]:
ALTER FULLTEXT INDEX ON [table_name] START FULL POPULATION
Or is this command implied by "reorganizing the full text catalog"?
August 5, 2013 at 3:36 pm
That depends on who you specified the CHANGE_TRACKING option when you created the index. If you specified MANUAL, you need to issue a command update the index. If you specifed AUTO (which is the default), you do not, unless the index is on a column of the deprecated text/ntext/image data type and you use WRITETEXT or UPDATETEXT.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 5, 2013 at 7:11 pm
Erland Sommarskog (8/5/2013)
If you specified MANUAL, you need to issue a command update the index.
If I have "MANUAL" or "DO NOT TRACK CHANGES", would I just execute this command to update the index:
ALTER FULLTEXT INDEX ON [table_name] START FULL POPULATION
Assuming that I execute the "FULL POPULATION" command to update the full-text index, why would I need to execute the following code on a nightly basis:
ALTER FULLTEXT CATALOG [catalog_name] REORGANIZE
August 6, 2013 at 1:07 am
No. But why would you do START FULL POPULATION every time? If you have change tracking on, you can do START UPDATE POPULATION instead. And in that case you may have to reorganize every once in a while.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 7, 2013 at 8:22 pm
Erland Sommarskog (8/6/2013)
No. But why would you do START FULL POPULATION every time? If you have change tracking on, you can do START UPDATE POPULATION instead. And in that case you may have to reorganize every once in a while.
Just to clarify, "REORGANIZE" of the fulltext catalog does not imply that "START UPDATE POPULATION" will execute? In other words, both these commands (i.e., REORGANIZE full text catalog & START UPDATE POPULATION for full teext index) accomplish separate goals and both need to be run on a scheduled basis to ensure that the full text index is up to date?
August 8, 2013 at 1:19 am
I have a confession to make: I have not worked much with fulltext indexes; only read a book and played a little with it.
For all my answers in this thread I have simply read Books Online.
But, yes, my understanding is that REORGANIZE and START UPDATE POPULATION achieves different goals, and one does not trigger the other. REORGANIZE makes your fulltext catalog more efficient. START UPDATE POPULATE makes your index up-to-date with regards to your data. It seems to me that you should first run START UPDATE POPULATION and when the population has completed, you should reorganise.
Note also that if you have CHANGE_TRACKING set to AUTO, there is no need run START UPDATE POPULATION.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply