12

I have this database table that is suppose to keep the full-text index up-to-date. However I am not seeing it happening at all (no error in the log because the last log I see was when I triggered it manually).

Here is what I am seeing...

full-text catalog properties

but on the table itself...

enter image description here

Could this be why it's not happening automatically?

  • For clarity, which log are you referring to? – Mike Zalansky Dec 14 '15 at 16:55
  • @mikezalansky its the full text log which can be found at (..\MSSQL\Log\SQLFT*) – Kin Shah Dec 16 '15 at 13:14
14
+50

You are looking at the wrong place.

You have to check as below :

enter image description here

enter image description here

Using T-SQL ..

Use database_name
go
ALTER FULLTEXT INDEX ON schema.table_name SET CHANGE_TRACKING AUTO;

Once done, you can check the status of the last populated datetime

-- script source : http://stackoverflow.com/a/10505496/1387418
-- Modified by Kin on Dec 14' 2015 to reflect the FTCatalogName
DECLARE @CatalogName VARCHAR(MAX)
SET     @CatalogName = 'AW2008FullTextCatalog' -- change here !

SELECT name as FTCatalogName, 
    DATEADD(ss, FULLTEXTCATALOGPROPERTY(@CatalogName,'PopulateCompletionAge'), '1/1/1990') AS LastPopulated
    ,(SELECT CASE FULLTEXTCATALOGPROPERTY(@CatalogName,'PopulateStatus')
        WHEN 0 THEN 'Idle'
        WHEN 1 THEN 'Full Population In Progress'
        WHEN 2 THEN 'Paused'
        WHEN 3 THEN 'Throttled'
        WHEN 4 THEN 'Recovering'
        WHEN 5 THEN 'Shutdown'
        WHEN 6 THEN 'Incremental Population In Progress'
        WHEN 7 THEN 'Building Index'
        WHEN 8 THEN 'Disk Full.  Paused'
        WHEN 9 THEN 'Change Tracking' END) AS PopulateStatus
FROM sys.fulltext_catalogs

enter image description here

Insert some data ..

insert into HumanResources.JobCandidate
(BusinessEntityID, Resume, ModifiedDate)
values 
    (4, NULL, DEFAULT)

Now you will see that the FT Catalog is updated.

enter image description here

In the logs (..\MSSQL\Log\SQLFT*)also, below will be the message...

2015-12-14 12:36:51.29 spid50s Informational: Full-text Auto population completed for table or indexed view '[AdventureWorks2012].[HumanResources].[JobCandidate]' (table or indexed view ID '1589580701', database ID '5'). Number of documents processed: 1. Number of documents failed: 0. Number of documents that will be retried: 0.

From BOL :

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.

By default, SQL Server populates a new full-text index fully as soon as it is created. However, a full population can consume a significant amount of resources. Therefore, when creating a full-text index during peak periods, it is often a best practice to delay the full population until an off-peak time, particularly if the base table of an full-text index is large. However, the full-text catalog to which the index belongs is not usable until all of its full-text indexes are populated.

Also, refer to Improve the Performance of Full-Text Indexes

  • Thanks! Issue I am looking at is why it doesn't seem to update at all until I manually rebuild the index. Not sure if it's related but the database that seems to be having this issue the most is one that is also being mirrored. – MetaGuru Dec 14 '15 at 18:53
  • Issue I am looking at is why it doesn't seem to update at all until I manually rebuild the index. have you set it as per what I showed. If there are a lot of changes, then its going to take some time, since The full-text index is updated in the background, however, so propagated changes might not be reflected immediately in the index. – Kin Shah Dec 14 '15 at 18:55
  • yeah I checked the places you showed and it was correctly configured like in your examples... strangely though the last time the logs say this index was updated was a while ago, weeks even, compare to the last time new rows were added... it would run when new rows are ADDED according to this right? – MetaGuru Dec 14 '15 at 19:36
  • 1
    Yes .. I tested and then posted as answer. – Kin Shah Dec 14 '15 at 19:42
1

For some reason, the full text indexer stopped autopopulating for one of my SQL 2012 databases after my secondary Availability Group database replica took over and became the primary replica.

After deleting the Full Text Index and recreating it on the table with auto populate on, it started autopopulating again. I used the SQL Management Studio and the Full Text Index creating wizard to recreate the index.

I was worried about how long it would take to run a full indexing of the data. I was pleasantly surprised that it only took about 5 minutes to reindex 3 text columns from a table with 547619 records.

The Table level change tracking settings referred to in the question remained disabled. So the Full Text Index change tracking is definitely different from the Table change tracking setting. You do NOT need to have table change tracking enabled for Full Text Indexing Change Tracking and Full Text Indexing autopopulate to work.

Running the following query from the database will reveal the last time your full text indexer ran a crawl.

  SELECT  * FROM sys.fulltext_indexes

When autopopulate is on, the crawl_end_date should change after you insert data into the table.

Also, you'll note that when you run a query that uses the full text index, it will show the latest data:

  select TOP 10 * from YOURDATATABLE where contains((YOUR_indexed_column),'YOURSEARCHTERM') order by YOURPRIMARYKEYCOLUMN desc

Additional References:

Article describing different methods of Full Text Search Population: https://msdn.microsoft.com/en-us/library/ms142575%28v=sql.100%29.aspx

Information on querying a full text indexed table: https://technet.microsoft.com/en-us/library/ms142571(v=sql.110).aspx#queries

Article on Full Text Search Architecture: https://technet.microsoft.com/en-us/library/ms142571(v=sql.110).aspx#architecture

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.