Outstanding batch wouldn’t clear on sys.dm_fts_outstanding_batches

Outstanding batch wouldn’t clear on sys.dm_fts_outstanding_batches

I had an issue where an outstanding batch wouldn’t clear on sys.dm_fts_outstanding_batches. This meant the full-text index was not working when queried using the CONTAINSTABLE syntax. I used a series of queries from Glen Allan Berry’s post to help me identify what the problem was with my full-text index.

I restarted the Full-Text service with no success. This seemed like the natural thing to do and my colleagues said that it had worked in the past when FTS had stalled. Unfortunately, this didn’t help the situation.

I then rebuilt the Full Text Catalog and still had no success. This took 5 minutes however the rebuild was blocked so would normally have been much quicker.

As the full-text index was built on an indexed view, my next idea was to rebuild the clustered index of the view. To my surprise, this actually solved the problem and I no longer had any outstanding FTS batches. My query using CONTAINSTABLE returned a success and the developers confirmed their application was fixed. In my opinion, this is well worth a try.

The point of sharing this post is the hope that it will help someone else with the same issue. It’s not my greatest ever post and I’m not certain why the index rebuild helped but its been a good lesson to me that I need to learn more about Full-Text Indexing for SQL Server. Anything else I learn will be used to add useful information to this blog post.

Any suggestions for good resources in the comments would be highly appreciated as this looks like an area that has been far from extensively covered by other bloggers.