Sql-server – Converting and removing full text files

full-text-searchsql serversql-server-2012

I have a database that was created many SQL Server versions ago that has Full Text files. It is currently on a SQL 2012 server. How can I confirm that there is no data in use in the Full Text index files so I can delete them?

Best Answer

To see if the index is in use you can use sys.dm_db_index_usage_stats. Here is an example query with some description.

T-SQL Script to Correct SQL Server Full Text Indexes Not Updating by Pablo Echeverria

SELECT [t].[name] [table_name], 
       [i].[name] [index_name],
       [fi].[change_tracking_state_desc], 
       [fi].[has_crawl_completed], 
       [fi].[crawl_type_desc], 
       [fi].[crawl_end_date],
       [ius].[last_user_update], 
       [ius].[last_user_seek],
       (SELECT [name]+',' FROM [sys].[fulltext_index_columns] [fc] INNER JOIN [sys].[columns] [c] ON [c].[object_id] = [fc].[object_id] AND [c].[column_id] = [fc].[column_id] WHERE [fc].[object_id] = [fi].[object_id] FOR XML PATH('')) [columns],
       (CASE WHEN [fi].[crawl_end_date] < ISNULL([ius].[last_user_update], [ius].[last_user_seek]) THEN 'ALTER FULLTEXT INDEX ON ['+[t].[name]+'] SET CHANGE_TRACKING MANUAL; ALTER FULLTEXT INDEX ON ['+[t].[name]+'] SET CHANGE_TRACKING AUTO' ELSE '' END) [Command]
FROM [sys].[fulltext_indexes] [fi]
INNER JOIN [sys].[indexes] [i] ON [i].[index_id] = [fi].[unique_index_id] AND [i].[object_id] = [fi].[object_id]
INNER JOIN [sys].[tables] [t] ON [t].[object_id] = [fi].[object_id]
LEFT JOIN [sys].[dm_db_index_usage_stats] [ius] ON [ius].[index_id] = [fi].[unique_index_id] AND [ius].[object_id] = [fi].[object_id] AND [ius].[database_id] = DB_ID()
ORDER BY [table_name], [index_name]

This article refers to more system objects regarding Full-Text Search. You can use this to find more details especially about population status.

Related Question