Two days back, I got a call from collegue asking a help on Full Text Serach. I was not really had good experience from the past, I thought I will give a try this time.

The issue was nothing but the crawling(population) is not taking place once the he disbled the Full text index for a testing purpose. Hence, the newly added rows were not fetching in the FULLTEXT.CONTAINS query.

As I mentioned, I am new to FTS, I thought of googling the subject and help my friend.

The first try was looking at the query to fetch the data.

The query used was as below:
Select casekey, cause
from <table_Name>
Where
CONTAINS(cause,  'testing' )

I suggested to use the following way to fetch the data:

Select casekey, cause
from <table_Name>
Where
FREETEXT(cause,  '"testing*"' )

But, we had no luck with this. The query was not fetching any data.

Later, I thought of checking the status of population: The following query can be used to check the status:

declare @id int
select @id = id FROM sys.sysobjects where [Name] = '<table_Name>'
select 'TableFullTextBackgroundUpdateIndexOn' as 'Property', objectpropertyex(@id, 'TableFullTextBackgroundUpdateIndexOn') as 'Value'
union select 'TableFullTextChangeTrackingOn', objectpropertyex(@id, 'TableFullTextChangeTrackingOn')
union select 'TableFulltextDocsProcessed', objectpropertyex(@id, 'TableFulltextDocsProcessed')
union select 'TableFulltextFailCount', objectpropertyex(@id, 'TableFulltextFailCount')
union select 'TableFulltextItemCount', objectpropertyex(@id, 'TableFulltextItemCount')
union select 'TableFulltextKeyColumn', objectpropertyex(@id, 'TableFulltextKeyColumn')
union select 'TableFulltextPendingChanges', objectpropertyex(@id, 'TableFulltextPendingChanges')
union select 'TableHasActiveFulltextIndex', objectpropertyex(@id, 'TableHasActiveFulltextIndex')

This gave the following results:

TableFullTextBackgroundUpdateIndexOn 1
TableFullTextChangeTrackingOn 1
TableFulltextDocsProcessed 0
TableFulltextFailCount 0
TableFulltextItemCount 4038
TableFulltextKeyColumn 1
TableFulltextPendingChanges 15763
TableHasActiveFulltextIndex 1

This was a good indication that there are pending changes to be indexed.

The next step I thought was the FULL population would have stopped or paused somehow. Hence I decided to start the FULL Population using the below query:

ALTER FULLTEXT INDEX ON dbo.CurrentPostFullTextThursday START FULL POPULATION;

Unfortunately, it was ended up with warning: a new population can not be started as currently running(The error message is not the exact one.). Then I thought, there might be some issue that would have paused the population. I wanted to resume the population as below.

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

Again, no luck as a warning message as only paused population can be resumed.

This point of time, we were no-where to continue with the issue with our limited knowledge. We thought of restarting the server. Somehow we thought of rebuilding our clustered index as final choice and do the server restart.

As per the plan, we enabled the Full text index , then we rebuilt the clustered index for the table and then enabled the Full text Index. It was wonder that the population started immediately and we could see the pending changes count was reducing over a period.

The final result was something like below:
TableFullTextBackgroundUpdateIndexOn 1
TableFullTextChangeTrackingOn 1
TableFulltextDocsProcessed 0
TableFulltextFailCount 0
TableFulltextItemCount 4038
TableFulltextKeyColumn 1
TableFulltextPendingChanges 0
TableHasActiveFulltextIndex 1

At the end, I am very happy that I learned something and could help my friend(Prabhu) on it.

I guess you enjoyed this blog!!!Request you to share your knowledge if you had come across these kind of issues in the past.