John McCormack DBA

SQL Server Databases and Cloud

  • Personal
    • About
  • Free Training
    • SQL Server on Amazon RDS (Free Course)
    • Free practice questions to help you pass DP-900
  • Save money in Azure
    • Azure IaaS SQL Backups – Stop burning money
    • Your Azure SQL Database and Managed Instance is too big
    • Turn the cloud off at bedtime to save 70%
    • Your Azure SQL Virtual Machine might be too big
    • Save money with Azure SQL DB serverless
    • Save up to 73% with reserved instances
    • Delete unused instances to save money in Azure
  • Hire me
    • 60 minute cost optimization
    • Let me solve your SQL Server problems
    • Take a look at my Sessionize speaker’s profile

Outstanding batch wouldn’t clear on sys.dm_fts_outstanding_batches

16th February 2019 By John McCormack Leave a Comment

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.

sys.dm_fts_outstanding_batches

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.

sys.dm_fts_outstanding_batches cleared

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.

Share this:

  • Tweet
  • Email

Related

Filed Under: front-page, Guides Tagged With: FTS, Full-Text Catalog, Full-Text Index, Full-Text outstanding batches

About John McCormack

John McCormack is an experienced SQL DBA with extensive knowledge of the two largest public clouds: AWS and Azure.

Leave a Reply Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy

John McCormack · Copyright © 2023

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.