1

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]

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.