I had a hardware issue this past weekend on one of my database servers where I am running SQL Server 2008 Integrated Full Text Search (iFTS). After the issue was resolved, I noticed a problem with iFTS, where changed items in the base relational tables were not being added to their respective full text indexes.

The way that I noticed this was by running a query like this:

-- Find out how many changes are pending in the Full Text index
SELECT OBJECTPROPERTY(OBJECT_ID('CurrentPostFullTextThursday'), 'TableFulltextPendingChanges') 
AS [Full Text Pending Changes];

If you are running with automatic change tracking, this query should return a result pretty close to zero (unless there is a problem). If you see a high value that does not go down after multiple queries, then you have a problem.

The first thing to try is this command:

-- Resume population in case of an error during manual or auto population
ALTER FULLTEXT INDEX ON dbo.CurrentPostFullTextThursday RESUME POPULATION;

More often than not, you should see the number of pending changes start to go down pretty quickly (depending on your hardware and workload). Full Text crawls tend to be I/O and memory dependent.  After some period of time, you should see the number of pending changes go down to near zero.

I also periodically run this query, to check the number of full text fragments in the entire database:

-- Check Full Text Fragments for entire database (lower number of rows is better)
-- Status Codes
-- 0 = Newly created and not yet used
-- 1 = Being used for insert
-- 4 = Closed. Ready for query 
-- 6 = Being used for merge input and ready for query
-- 8 = Marked for deletion. Will not be used for query and merge source.
SELECT OBJECT_NAME(table_id) AS [Table Name], fragment_id, fragment_object_id, 
       [status], data_size, row_count 
FROM sys.fulltext_index_fragments WITH (NOLOCK);

The number of fragments will vary based on how volatile your base relational data is. You will get better Full Text search performance when you have a lower number of fragments, but maintaining a lower number of fragments puts extra stress on your system during Full Text crawls. If you see a very high number of fragments for a particular table, you can run a manual merge (passing in the name of the full text catalog, not the base table).

-- Start a Manual Merge (when fragment count is high)
ALTER FULLTEXT CATALOG ctCurrentPostFullTextEx REORGANIZE;

Doing a Manual Merge can be very resource intensive, so it is best to do it during off-peak times if possible.