patternModerate
Will a Full-Text Catalog with Track changes: AUTO automatically update if Change Tracking for that table is not enabled?
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?
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 ..
Once done, you can check the status of the last populated datetime
Insert some data ..
Now you will see that the FT Catalog is updated.
In the logs (
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
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_catalogsInsert 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_catalogsinsert into HumanResources.JobCandidate
(BusinessEntityID, Resume, ModifiedDate)
values
(4, NULL, DEFAULT)Context
StackExchange Database Administrators Q#123418, answer score: 18
Revisions (0)
No revisions yet.