I had a query from an attendee of my full-text indexing session at TechEd US. He asked how he can find out which documents (or how many) still need to be processed. I did a little investigation on this and here's my best guess:
<WARNING: Undocumented and potentially just a guess!>
1. Query for the objectid of your full-text index. You can do this by:
select * from sys.fulltext_indexes
2. Open an admin connection to your system ie: connect to admin:SERVER instead of SERVER.
3. Query as follows:
select * from sys.fulltext_index_docidstatus_2105058535
(the number on the end needs to be your full text index's object id not mine :-))
From what I can see, this table seems to hold details of documents not yet processed and it gets cleaned up as documents are processed. This is an internal table that you can see via:
select * from sys.internal_tables
</WARNING: Undocumented and potentially just a guess!>
Hope that helps someone.
3 thoughts on “SQL Server 2008 Full Text Indexing – Working out how many documents still need to be processed”
This query seems to work pretty well for getting the number of pending changes:
— Find out how many changes are pending
SELECT OBJECTPROPERTY(OBJECT_ID('CurrentPostFullTextMonday'), 'TableFulltextPendingChanges') AS 'Full Text Pending Changes';
You will have to change CurrentPostFullTextMonday to your FT index name.
Thanks, yep, that'll work for the situation where we only want to know the number, rather than details on which documents are still outstanding. Often that's all that's needed so that's excellent thanks!
this would be display an error