Verify if full text index is indeed successful on a table

Question

Hi,
We are using SQl 2005\2008.
In our application we create a table and full text index on that table at run time and insert data into it.

Once insertion of data is done, we need to check whether the Full text indexing on this particular table is indeed successful.
Based on success\failure, we need to perform certain tasks.

Which properties  do I query to determine the state to be absolutely sure of either
success\failure ?

I have checked MSDN and following properties:
Table Full-Text Item Count -  on success, this should be equal to the no. of rows in the table?
Table Full-Text Fail Count  - on success, this should be 0?
Table Full-Text Docs Processed - on success, should this be ideally 0?
Table Full-Text PendingChanges - on success, should be 0?

I have noticed in my test scenario that "Table Full-Text Docs Processed" is non zero
even if all other properties are giving expected values.

Any idea, why?

Can I assume that if (Table Full-Text Item Count = table.row count) then FTI is successful?

Are there any other properties that I need to query?

Please advice.

Thanks and Regards
Tanu

Answer 1

You can query the status using FULLTEXTCATALOGPROPERTY

SELECT
    FULLTEXTCATALOGPROPERTY(cat.name,'ItemCount'),
    FULLTEXTCATALOGPROPERTY(cat.name,'MergeStatus'),
    FULLTEXTCATALOGPROPERTY(cat.name,'PopulateCompletionAge'),
    FULLTEXTCATALOGPROPERTY(cat.name,'PopulateStatus'),
    FULLTEXTCATALOGPROPERTY(cat.name,'ImportStatus')
FROM sys.fulltext_catalogs AS cat

I actually use the following T-SQL statement to ask if the population status of the full text index is Idle:
SELECT OBJECTPROPERTY(object_id('v_doc_desc_de'), 'TableFulltextPopulateStatus')

 'v_doc_desc_de' is the name of the database view that we index.

If the population status is not idle, I wait a couple of seconds and ask again, until it is Idle.

The MSDN documentation states that the OBJECTPROPERTYEX function (at table level) is recommended over the FULLTEXTCATALOGPROPERTY statement with property 'PopulateStatus'.

Thanks,

Debasish

Answer 2

Thanks for the reply.

I am using ObjectPropertyEx function too.

What I want to know is,

if (PopulateStatus=idle ) and (itemcount==table.rowcount)

Answer 3

sorry, the above incomplete post got sent by mistake.

I am using ObjectPropertyEx function too.

What I want to know is, whether all items\rows in the table are successfully indexed.

i.e say, there were 6 rows in my table to be indexed.

So,

if (OBJECTPROPERTY(object_id(mytable), 'TableFulltextPopulateStatus') ==idle)

and FULLTEXTCATALOGPROPERTY(cat.name,'ItemCount') == 6)

)

 then   "all rows are successfully indexed"

else

       "not all rows were indexed"

Is this the only condition I need to check for success\failure of FTI population?

Based on this I need to notify users that thier full text search may or may not work.

Regards

Tanu