2

I have a database with a Full Text Catalog enabled, however the Catalog hasn't updated since about 4 weeks ago.

There are no errors in the event logs, the SQL Server logs or the last SQLFT log file, which is also timestamped at the last time the Catalog updated, and has no errors in the file. The FT daemon last restarted around 6 weeks ago, and is running

The table change tracking is set to "Automatic" and Full-Text Indexing is enabled.

In the catalog, the table/column has track changes set to automatic.

Queries against the Full Text Index are returning data up to the point that the catalog was last updated, but nothing newer.

select * from sys.dm_fts_index_keywords( DB_ID('db'), 
OBJECT_ID('dbo.table') )

returns 198k rows of data.

DECLARE @CatalogName VARCHAR(MAX)
SET     @CatalogName = 'dbFullTextCatalog' -- 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

Returns the Last populated as 4 weeks ago and the current status as Idle (0).

Any ideas what may have caused the catalog to stop updating, and what the resolution may be? This is a production system so I cannot "just restart the server"

Thanks in advance for any ideas what may be causing this!

  • I've had that happen with production servers in 2008 R2. I never found the reason why. The only way to fix it was to repopulate the Full Text Catalog (Update or Full). – hot2use Aug 24 '17 at 12:22
  • 1
    Thanks @hot2use - In the end I have toggled the table Change Tracking from Auto to Manual and back to Auto, which has kicked it back into life... Not what I would call a fix but it resolves the issue until it happens again! – Carl Aug 25 '17 at 8:20

Your Answer

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

Browse other questions tagged or ask your own question.