I ran sp_Blitz on a server and it indicated that full text indexes were not populating. I got a list of the indexes that are not populating and looked at the definitions of the indexes. The syntax of those indexes does not look like BOL examples for full text index creation, but instead they look like traditional row indexes. And they are on BigInt data types not text. Here is the code that returns the full text indexes that are not populating.
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;
I'm not particularly familiar with full text indexing but it seems sp_Blitz is highlighting full text index issues that aren't really full text indexes. Just confused by what I'm seeing.
Here is an example of the DDL of one of the indexes that is identified as a full text index. but, it appears to me to be a traditional row based index. The DDL doesn't look like what would be used to create a full text index.
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tblIntAnalysedItems_106]') AND name = N'PK_tblIntAnalysedItems_106') ALTER TABLE [dbo].[tblIntAnalysedItems_106] ADD CONSTRAINT [PK_tblIntAnalysedItems_106] PRIMARY KEY CLUSTERED ( [RowId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [SomeObject]