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?


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],
       (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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.