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;