Problem
Recently, we experienced an issue in production with SQL Server Full Text Indexes. Users complained the following query didn’t return records, even when there were records that matched the condition and the query returned records in the development and test environments:
SELECT * FROM [wo] WHERE CONTAINS([de], 'FORMSOF (INFLECTIONAL, "steel")')
How do we resolve the issue, and make sure this doesn’t occur in the future?
Solution
After making sure there were records that matched the condition using the LIKE operator, and making sure the full text catalog was properly configured (enabled, to automatically track changes and set up on the corresponding table and columns), we noticed the full text catalog was not updated recently after checking the full text catalog properties in SQL Server Management Studio (SSMS) for the property "Last population date" as shown in the images below. To get to this screen, go to the database and then go to Storage > Full Text Catalogs, find the full text catalog you want to work with and right click on it and select Properties. The second image shows the Last Population Date and this looked like it was up to date.
Troubleshooting the Full Text Catalog Issue
After a lot of research, we found these articles from Brent Ozar and StackExchange telling us this issue has been present since SQL Server 2008, and we discovered the possible ways to fix the full text search:
- Enable population schedule on the table. This was already confirmed, so not an option.
- Repopulate the full text catalog (update or full). Since this catalog contained 24 million items from 162 different tables, it was also not an option in order to not continue affecting the production environment. We also didn’t know if all tables were affected or only this table, so this could have been a waste of resources.
- Toggle the change tracking from Auto to Manual and back to Auto. This was the preferred option. This can be done on the Tables/Views page for the full text catalog. Make the change and click OK to save and then change back and click OK to save.
We were able to fix the issue for the individual table, but how do we know if there are other tables affected? And how can this be automated so no manual intervention is required? The answer is the script below, which checks the full text catalogs and tables, provides insight on how they are set up, gives you the columns contained in the full text index, and also gives you a T-SQL command to toggle the settings for the individual table if needed.
Script to Generate T-SQL Command to Toggle Full Text Catalog Settings
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]. [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('')) ,
(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]
The script works in the following way:
- Gets all full text indexes from the current database, to get the configuration and the last crawl date.
- Joins with the indexes to get the index name, and with the tables to get the table name.
- Joins with the index usage stats, to get the last user update and last user seek.
- With a subquery, gets the columns included in the full text index.
- If the crawl end date is earlier than the last user update/seek, display a command to re-populate it.
After running the query in the affected database, here are the results:
From this output, there are some things that need to be noted:
- The configuration is correct, it is set up as automatic, it has already finished the crawl, and it was an UPDATE.
- We don’t know when the last user update was performed on the table, so we’re comparing it against last user seek, which can throw false negatives.
- From the first record, we can see the table was last accessed on a date earlier than the last crawl date, so there is no action to be done.
- From the second record, we can see the table was accessed recently, but the last crawl date was done earlier than that. This means there’s a chance the catalog is not up to date with the latest records, and there’s a need to run the command contained in the "Command" column.
So how do we proceed with the ones that may need an update? We can run the generated command as is, and it is going to update the catalog and the crawl end date, so you won’t see this result anymore and will be sure it is up to date.
Another option is to manually check the table to confirm if it really needs an update. We query the table that has issues (including the column on which the full text catalog is configured, in this example it is named "description") to find the latest records and this is what we get:
We can see the latest record was inserted and updated on 01/17/2019, and the crawl end date from the image above shows 02/24/2019, so there’s no need to update the full text catalog, but it won’t harm if we do. Note that this will depend on the fields in the table, in case there is no way to tell which record is the latest, we don’t know if it is up to date or not.
We can also confirm the full text search is going to return the results by running a query like this:
SELECT [description], [createdate], [statusdate], [rowstamp] FROM [cXXX] WHERE [description] = 'Notification XXX.' AND CONTAINS([description], 'Notification')
Then we can confirm the same row from earlier is returned. If it isn’t, then the catalog needs to be updated.
You can use the Command output column from the script above. This will toggle the setting to Manual and then back to Auto. You can do this for each one, but if there are a lot this can get tiresome.
Automating this Process
Finally, how can we automate this? We can create a scheduled job that runs this script and checks if there is any row that has something in the "Command" column, and we can either notify a distribution list or run the commands from all rows returned. The schedule will depend on how critical it is to have the data up to date.
Next Steps
- Please check this link for a script that tells you when full text index population has finished, or if it’s in progress.
- Please check this link to gather information for SQL Server full text catalogs.
- Please check this link for system stored procedures that tell you information about your full text catalogs.
- Please check this link for system configuration information locations for full text indexes.
