A few days ago i came across the below message in our SQL Server logs:
Error: 30089, Severity: 17, State: 1.
The fulltext filter daemon host (FDHost) process has stopped abnormally. This can occur if an incorrectly configured or malfunctioning linguistic component, such as a wordbreaker, stemmer or filter has caused an irrecoverable error during full-text indexing or query processing. The process will be restarted automatically.
Full-text search is a key component in our SQL environment as we support a database that consists of millions of documents. I started scanning the FT crawl log and also found the below error:
Error ‘0x80004005’ occurred during full-text index population for table or indexed view ‘[DBNAME].[dbo].[TABLENAMe]’ (table or indexed view ID ‘10045673’, database ID ‘6’), full-text key value ‘4’. Attempt will be made to reindex it.
In case you are not aware as to where to find the crwal log then go the following location:
%ProgramFiles%\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\LOG folder- this is where log folder would be for SQL logs. Under this folder you will find files with following pattern:
SQLFT<DatabaseID><FullTextCatalogID>.LOG[<n>]
Where DatabaseID is
The ID of a database. <dbid> is a five digit number with leading zeros.<FullTextCatalogID>
Full-text catalog ID. <catid> is a five digit number with leading zeros.
n an integer that indicates one or more crawl logs of the same full-text catalog exist.
For example, SQLFT00006000010.5 is the crawl log file for a database with database ID = 5, and full-text catalog ID = 10. The 5 at the end of the file name indicates that there are two crawl log files for this database/catalog pair.
I searched the msdn site with error message and this is what i found as the cause of the error:
This problem occurs because of a bug in the communication between the Sqlservr.exe process and the Fdhost.exe process. When batches are sent from the Sqlservr.exe process to the Fdhost.exe process, sometimes the outbound shared memory (OSM), which is the shared memory from Sqlservr.exe to Fdhost.exe, has insufficient space to fit the next column of a document. When this occurs, the Sqlservr.exe process sends a message to notify the Fdhost.exe process to process all the existing data in the OSM. While the Fdhost.exe process is processing data, the inbound shared memory (ISM), which is the shared memory from Fdhost.exe to Sqlservr.exe, may become full. In this situation, the Fdhost.exe process sends a “rewind” message to the Sqlservr.exe process. This message requests that the Sqlservr.exe process resend the last document from the first column. However, when the Sqlservr.exe process starts to send the document again, it starts from column 2 instead of column 1.
Resolution:
Apply Cumulative update package 3 for SQL Server 2008 R2 Service Pack 2 and Cumulative update package 4 for SQL Server 2008
MS also suggests an workaround for the same which is increasing the ism size value:
What is ISM. Well ISM is the internal shared memory used by the FDHost.exe process.
To show the current value:
sp_fulltext_service ‘ism_size’
To change the value to a new value:
sp_fulltext_service ‘ism_size’,@value=NewValue
Useful tech articles of Full-text search:
http://msdn.microsoft.com/en-IN/library/ms142560%28v=sql.100%29
http://msdn.microsoft.com/en-us/library/cc721269%28SQL.100%29.aspx
Applied SQL... 