September 8, 2017 at 2:22 am
Is there *any* way to determine how far a fulltext index rebuild has progressed? I have a table which has taken over 4 days to rebuild and I have no way to tell whether it's still working or simply *says* it's working. I can see three "FT CRAWL" processes for the database showing in sp_who2.
FULLTEXTCATALOGPROPERTY('catalog', 'PopulateStatus') is returning 1 (full population in progress) for the relevant table.
Are there any monitoring views that report rows processed, rows left, etc. or anything else I can review to see what's happening? I haven't had any success in my searches so far.
Thanks.
September 8, 2017 at 3:28 am
You can get some information from here: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-fts-index-population-transact-sql
However, I'm not sure this will do more than give you an idea of how many more items need to be indexed with outstanding batches.
September 8, 2017 at 4:05 am
Your article led me to https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-fts-population-ranges-transact-sql.
Do you know whether the "processed_row_count" value in this view is accurate? If so then it's only 26% complete and has 6 more days to go!
September 8, 2017 at 5:19 am
I don't. been a long time since I've used the DTS system at any scale, and haven't tracked the rows here. I'll try to set up a test and see if this is actually tracking rows.
September 8, 2017 at 5:48 am
Thanks. I've also been looking at the log files for this (SQLFT*.LOG) and notice in a previous rebuild that there were messages of the form:
Error '0x80043630: The filter daemon process MSFTEFD timed out for an unknown reason. This may indicate a bug in a filter, wordbreaker, or protocol handler.' occurred during full-text index population for table or indexed view '[db].[schema].
' (table or indexed view ID '0', database ID '0'), full-text key value '0'. Attempt will be made to reindex it.
A posted "fix" to this problem is "sp_fulltext_service 'restart_all_fdhosts'" but I'm concerned that this will cause the problematic rebuild to start over.
September 11, 2017 at 8:39 am
It might. The FTS engine got a lot of work in 2005/2008. Not a lot since.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply