August 25, 2020 at 8:22 am
Hi
We have a problem on a customer 2014 SQL Server.
We have a fulltext solution, running on many vessels and offices around the world. So the actual solution works.
On this specific vessel we had a problem with Office and PDF iFilters. They have now been installed, and seems to work.
If we add a new file to our DocumentFile table, it is correctly FullText indexed and a CONTAINS search can find the record.
But, if we do a full repopulation of the Full Text Index Catalog the Full Text log is flooded with errors, and most of the documents fails to be indexed.
I have tried to narrow the 350mb Full Text log to a few interesting cases with regards to the DocumentFile table
2020-08-25 05:53:13.54 spid302s Error '0x8004fd0a: Occurrence counter overflow, document is not indexed.' occurred during full-text index population for table or indexed view '[product].[dbo].[DocumentFile]' (table or indexed view ID '299329768', database ID '5'), full-text key value '0BE51707-7068-45FA-A028-A48BDEDDEE60'. Failed to index the row.
2020-08-25 05:53:43.48 spid299s Error '0x8004362f: A previous error prevented further processing of the batch.' occurred during full-text index population for table or indexed view '[product].[dbo].[DocumentFile]' (table or indexed view ID '299329768', database ID '5'), full-text key value '28BEE4BE-05DD-44CB-A944-F4B1D3AD8DC2'. Attempt will be made to reindex it.
On examining one of them, it seems to relate to this thread - https://social.msdn.microsoft.com/Forums/en-US/eff381f8-0972-4a88-b8e4-ce23dd074f16/fulltext-indexing-fails-with-0x8004fd0a-occurrence-counter-overflow?forum=sqlsearch
It was a .msg file, with .pdf attachments.
Doing an update on the table for that specific row doesn't make it indexed like it does with .docx files not indexed by repopulation.
UPDATE DocumentFile SET FileStream = FileStream WHERE GUID = '0BE51707-7068-45FA-A028-A48BDEDDEE60'
My question now is how I debug further what is wrong here? I find debugging full text very tough. Especially when the FT log file is 300mb plus.
And secondly, are there any other way to get my table repopulated (when full repopulation doesn't work) other than updating 30k+ files and hoping the change-tracking population works.
Gathering information for the thread, I found that Compatibility Level for the database is 2008 - could that affect this fix: https://support.microsoft.com/da-dk/help/3208276/update-improves-handling-of-documents-too-large-for-full-text-search-i
I hope someone out there has a hint or idea - I am running out of ways to attack this.
Best regards
/Anders
Post notes
The log does contain other rows, observed a lot of times. But that seems to be on other tables - but refer to Error '0x80004005' and '0x80040e97'
August 25, 2020 at 9:19 am
Update - just as I pressed send on the post, another thing came to my mind,.
I tried to update a PDF file record, from the assumption that maybe it was the PDF iFilter that was broken.
Doing that after a recycle of the fulltext crawl log gave me the following error.
2020-08-25 08:56:59.08 spid36s Error '0x8004fd02: The filter daemon MSFTEFD failed to load an IFilter interface for document, so it can't be indexed.' occurred during full-text index population for table or indexed view '[product].[dbo].[DocumentFile]' (table or indexed view ID '299329768', database ID '5'), full-text key value 'E7ABBBCB-EEFD-4E23-AEBD-2BFB6D033D7E'. Attempt will be made to reindex it.
So something might be broken for the Pdf iFilter - causing the FT Daemon to stop processing documents.
I will request a reinstall of the PDF iFilter - and get back with a conclusion.
August 26, 2020 at 6:58 am
Hi
Just to follow up.
Getting the PDF iFilter working correctly solved the case, and made indexing much better.
I tried adding the path to iFilter installation in system PATH.
https://blog.sqlauthority.com/2018/02/12/sql-server-full-text-search-not-working-pdf-documents/
I was a bit reluctant, as this has never been necessary at any other customer site.
But after doing that, restarting FD Launcher service, and restarting each FD Host, ensuring both of them get new PID, and thus must have loaded PATH from OS again - it was working better.
The server was a Windows 2008, with a SQL 2014. Most likely had been a working SQL2008 on Win 2008 with iFilters correctly installed. And then when installing SQL 2014 beside it, something has gone wrong.
We still have an issue with the Office iFilter, it works fine for msg, docx etc, but xlsx files are not indexed.
And xls files fail with an OS error similar to this: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/7c86aa67-6f0b-49ab-867f-994f07e3e7bd/full-text-search-know-which-row-has-error-in-indexing?forum=sqldatabaseengine
But for now I will leave it be, as PDF and Docx where the important ones.
August 27, 2020 at 12:12 am
I'm of no help here. I just wanted to say "thanks" for the followups on your problem... it's bound to help someone in the future and that might even be me someday. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply