HiveBrain v1.2.0
Get Started
← Back to all entries
patternModerate

Will a Full-Text Catalog with Track changes: AUTO automatically update if Change Tracking for that table is not enabled?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
trackingfullupdatetrackenabledwithautotextautomaticallywill

Problem

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...

but on the table itself...

Could this be why it's not happening automatically?

Solution

You are looking at the wrong place.

You have to check as below :

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


Insert some data ..

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


Now you will see that the FT Catalog is updated.

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

Code Snippets

Use database_name
go
ALTER FULLTEXT INDEX ON schema.table_name SET CHANGE_TRACKING AUTO;
-- 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
insert into HumanResources.JobCandidate
(BusinessEntityID, Resume, ModifiedDate)
values 
    (4, NULL, DEFAULT)

Context

StackExchange Database Administrators Q#123418, answer score: 18

Revisions (0)

No revisions yet.