full-text index not updating (SQL 2005)

Question

I have a table that was doing a fine job of full-text search until last week. There are over seven million records, and I see it has over seven million entries in the full-text index.

But nothing more recent than a week old shows up in the search results. I can see in the table's full-text index properties that there are a growing number (maybe 30 thousand today) of "pending" entries, probably corresponding to stuff it hasn't added to the index.

The first thing I checked was whether the index was enabled. It wasn't, so I enabled it, and the process that started the "ALTER FULLTEXT INDEX" job started blocking all the other requests on the server, so I killed it.

I have confirmed that the Full-Text Indexing service is running and starts automatically. I have also confirmed that it's automatically checking for changes, although turning that from "Automatic" to "Manual" to "Off" doesn't seem to do much except reset the number of queued results. (Turning it back to "Automatic" resets the number to 30,000.)

I have tried to rebuild the full-text index, but for "Full" and "Incremental" it throws back a message about "there is already an index". "Update" says "command completed successfully" but doesn't seem to have any effect.

The last thing I did today was:

1. disable the full-text index
2. rebuild the clustered index that the FTI is based on
3. re-enable the FTI

This started off an "ALTER FULLTEXT INDEX" job that, again, blocked all processes that were doing actual work.

We are trying to decide between Option A: wiping the existing FTI and rebuilding from scratch, and Option B: doing the disable / rebuild / re-enable trick, which appears to start something useful. (We would do B then A, but we're anticipating an hour or two of downtime for either, and A has worked in the past.)

Is there a faster way to do any of this? And any idea how I got in this situation in the first place?

Answer 1

That's an interesting problem. You got yourself into it I think because it was disabled. I may have found a way to get you out of it without blocking everything else.

First, know that this behavior is a bug in SQL Server 2005 pre SP1, make sure you have SP1.

If you already do, try:

ALTER FULLTEXT INDEX ON tablename SET CHANGE_TRACKING MANUAL

ALTER FULLTEXT INDEX ON tablename START UPDATE POPULATION

If that works then I think you've run into the same issue as this guy. He just set a job to run every hour or so. Granted, it's a temporary solution but it will give you time until you can actually rebuild the index.

Something else that just came to mind, have you tried REORGANIZEing the index? I know it doesn't block but I'm not sure if it will cause the flush to happen in such a way to keep it from blocking too.

Answer 2

Just to clarify you have checked that the partition the index is created on has space. We ran into this problem in our DEV environment where the full text index would not update.