SQL Server 2008 FREETEXT Search stopped working

Question

I recently had an issue where a FREETEXT search stopped working in my stored procedure.

SELECT ShowID
FROM Table
WHERE FREETEXT(Category, @Category)

This has been working for months until today. I kept trying a few things and then the only thing that worked was by me going into my full text catalog and removing it on the Category column and then adding it back in.

Has anyone else had this issue and if so do you know why it happened? We use the FREETEXT on several columns in several tables and I am concerned it might be happening elsewhere without us knowing.

Answer 1

I use these queries to monitor the state of my full text indexes. In the first query, the PopulateStatus should be "0 - Idle". In the second query the CrawlCompleted column should be "1".

-- FullText Catalog Info
SELECT @@SERVERNAME ServerName
, DB_NAME() DB
, C.fulltext_catalog_id CatalogID
, C.name CatalogName
, C.path CatalogPath
, CONVERT(NVARCHAR(10), FULLTEXTCATALOGPROPERTY(C.name, 'PopulateStatus')) + ' - '
    + CASE FULLTEXTCATALOGPROPERTY(C.name, '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 is full, paused'
        WHEN 9 THEN 'Change tracking'
        ELSE '** Unknown PopulateStatus Code **' 
    END  PopulateStatus 
, FULLTEXTCATALOGPROPERTY(C.name, 'IndexSize') CatalogSizeMB
, FULLTEXTCATALOGPROPERTY(C.name, 'ItemCount') ItemCount
, FULLTEXTCATALOGPROPERTY(C.name, 'UniqueKeyCount') UniqueKeyCount
, c.is_default IsDefaultCatalog
, c.is_accent_sensitivity_on IsAccentSensitive
, c.data_space_id DataSpaceID
, c.file_id FileID
, c.principal_id PrincipalID
FROM sys.fulltext_catalogs C
ORDER BY C.name;

-- FullText Indexed Table/View info
SELECT C.name CatalogName
, S.name FTItemSchema
, O.name FTItemName
, O.type_desc FTItemType
--, I.object_id 
--, I.unique_index_id
, SI.name UniqueIndexName
--, I.fulltext_catalog_id
, I.is_enabled IsEnabled
, I.change_tracking_state_desc ChangeTracking
, I.has_crawl_completed CrawlCompleted
, I.crawl_type_desc CrawlType
, I.crawl_start_date CrawlStart
, I.crawl_end_date CrawlEnd
FROM sys.fulltext_indexes I
INNER JOIN sys.fulltext_catalogs C ON C.fulltext_catalog_id = I.fulltext_catalog_id
INNER JOIN sys.objects O ON O.object_id = I.object_id
INNER JOIN sys.schemas S ON S.schema_id = O.schema_id
INNER JOIN sys.indexes SI ON SI.object_id = I.object_id AND SI.index_id = I.unique_index_id
ORDER BY C.name, S.name, O.name

-- FullText Indexed Column info
SELECT C.name CatalogName
, S.name FTItemSchema
, O.name FTItemName
-- , IC.object_id
, IC.language_id
, FIC.name IndexedColumnName
, FIT.name + '(' + CASE FIC.max_length WHEN -1 THEN 'max' ELSE CONVERT(NVARCHAR(20), FIC.max_length / (CASE WHEN FIT.name IN ('nchar', 'nvarchar') THEN 2 ELSE 1 END ) ) END + ')' IndexedColumnType 
, FIC.is_nullable IndexedColumnNullable
, TIC.name TypeColumnName
, TIT.name + '(' + CASE TIC.max_length WHEN -1 THEN 'max' ELSE CONVERT(NVARCHAR(20), TIC.max_length / (CASE WHEN TIT.name IN ('nchar', 'nvarchar') THEN 2 ELSE 1 END ) ) END + ')' TypeColumnType
, TIC.is_nullable TypeColumnNullable
FROM sys.fulltext_index_columns IC
INNER JOIN sys.fulltext_indexes I ON I.object_id = IC.object_id
INNER JOIN sys.fulltext_catalogs C ON C.fulltext_catalog_id = I.fulltext_catalog_id
INNER JOIN sys.objects O ON O.object_id = IC.object_id
INNER JOIN sys.schemas S ON S.schema_id = O.schema_id
INNER JOIN sys.columns FIC ON FIC.object_id = IC.object_id AND FIC.column_id = IC.column_id
INNER JOIN sys.types FIT ON FIT.user_type_id = FIC.user_type_id
LEFT JOIN sys.columns TIC ON TIC.object_id = IC.object_id AND TIC.column_id = IC.type_column_id
LEFT JOIN sys.types TIT ON TIT.user_type_id = TIC.user_type_id
ORDER BY C.name, S.name, O.name, IC.column_id