Matt_Jsy
asked on
SQL Server 2008 Full Text Index re populating despite no changes to table
Hello,
I have full text indexing set on a table. When I set up the index I ran a full population and left the track changes as Auto. The full population ran and I tested that full text queries were returning correct results, however I have looked at the index again today only to find that its status is "Populating Catalog". The problem is there have been no changes to the database since the full population was run yesterday, no inserts, updates or deletes. It is a test environment and only I have access to the full text indexed table and therefore know nothing has been changed.
Is there any reason why SQL Server runs a new population despite there being no changes to the table, or in fact to the database?
As mentioned above it is set to Change Tracking Auto so the indexes should be amended when data is changed not at random.
Thanks
I have full text indexing set on a table. When I set up the index I ran a full population and left the track changes as Auto. The full population ran and I tested that full text queries were returning correct results, however I have looked at the index again today only to find that its status is "Populating Catalog". The problem is there have been no changes to the database since the full population was run yesterday, no inserts, updates or deletes. It is a test environment and only I have access to the full text indexed table and therefore know nothing has been changed.
Is there any reason why SQL Server runs a new population despite there being no changes to the table, or in fact to the database?
As mentioned above it is set to Change Tracking Auto so the indexes should be amended when data is changed not at random.
Thanks
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
Guy Hengel [angelIII / a3]
is the corresponding msdb job running? is it actually created?
ASKER
When I created the full text index I also ran a full population at the time. This was yesterday. Once the full text indexing was initially populated the status changed to Idle. It is set on auto tracking so no job was created, if you mean a scheduled job as I did not need one when set to auto track changes?
Today, despit no updates or inserts or deletes the full text status was at one point "Populating", yet it is my understanding it should not have been because there were no changes since it last ran.
I also ran test when it first populated to make sure full text queries where returning the expected number of rows in the table.
Today, despit no updates or inserts or deletes the full text status was at one point "Populating", yet it is my understanding it should not have been because there were no changes since it last ran.
I also ran test when it first populated to make sure full text queries where returning the expected number of rows in the table.
ASKER
I thought that the full text update crawls happened after an update, insert or delete but it does not appear that is the case and that they just run at certain intervals, I have not set up a schedule hence I was expecting the updates to trigger a crawl and could not figure out why they were happening with no update to start them off.
Can anyone confirm this is the case?
Thanks
Can anyone confirm this is the case?
Thanks
I (re-)confirm that you need to set up the schedule to get your full-text index population updated.
setting "track changes to auto" will only make sure that the update will be as efficient as possible, but it will not automatically do the update.
it's some time that I did configure full text indexes, but there are some config screens for this...
http://technet.microsoft.com/en-us/library/ms142575.aspx#create
setting "track changes to auto" will only make sure that the update will be as efficient as possible, but it will not automatically do the update.
it's some time that I did configure full text indexes, but there are some config screens for this...
http://technet.microsoft.com/en-us/library/ms142575.aspx#create
ASKER
Thanks, I am a little confused in the link it states the following under automatic population. It sounds to me that it is automatic, or at least meant to be. I cant seem to find anything that says when it will take place or how often etc
Automatic population
By default, or if you specify CHANGE_TRACKING AUTO, the Full-Text Engine uses automatic population on the full-text index. After the initial full population completes, changes are tracked as data is modified in the base table, and the tracked changes are propagated automatically. The full-text index is updated in the background, however, so propagated changes might not be reflected immediately in the index.
Automatic population
By default, or if you specify CHANGE_TRACKING AUTO, the Full-Text Engine uses automatic population on the full-text index. After the initial full population completes, changes are tracked as data is modified in the base table, and the tracked changes are propagated automatically. The full-text index is updated in the background, however, so propagated changes might not be reflected immediately in the index.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
I have split the answer into multiples because although I think I have answered my own problem I used the links that AngelIII gave me to get there even though I believe we were talking about different methods of populating the indexes. Therefore I think it is fair to give half of the points.