Full Text Indexes Not Populating

SQL Server’s full text indexes are not like regular indexes – they don’t necessarily keep fully up-to-date with the rest of the table.

To find this alert, we’re checking sys.fulltext_indexes looking for indexes that are enabled, but where the crawl last finished more than a week ago.

To Fix the Problem

Look at the database in question, and check the full text indexes using this query from Mark Freeman:

SELECT	FTCatalogName	= c.name
    ,TableName	= t.name
    ,IndexName	= i.name
    ,LastCrawlEnd	= fi.crawl_end_date
FROM	sys.fulltext_indexes fi
    JOIN sys.tables t			ON t.[object_id] = fi.[object_id] 
    JOIN sys.fulltext_catalogs c	ON fi.fulltext_catalog_id = c.fulltext_catalog_id
    JOIN sys.indexes i			ON fi.unique_index_id = i.index_id AND fi.[object_id] = i.[object_id]
 WHERE	fi.is_enabled		= 1
   AND	fi.crawl_end_date	< DATEADD(DAY, -7, GETDATE())
 ORDER	BY fi.crawl_end_date, t.name, i.name;

SELECT FTCatalogName = c.name

,TableName = t.name

,IndexName = i.name

,LastCrawlEnd = fi.crawl_end_date

FROM sys.fulltext_indexes fi

JOIN sys.tables t ON t.[object_id] = fi.[object_id]

JOIN sys.fulltext_catalogs c ON fi.fulltext_catalog_id = c.fulltext_catalog_id

JOIN sys.indexes i ON fi.unique_index_id = i.index_id AND fi.[object_id] = i.[object_id]

WHERE fi.is_enabled = 1

AND fi.crawl_end_date < DATEADD(DAY, -7, GETDATE())

ORDER BY fi.crawl_end_date, t.name, i.name;

The schedules may be turned off. Consider enabling them during your next maintenance window.