January 29, 2014 at 12:52 pm
Full Text Searches are working on my test server, but not on my production server. My test scenario is as follows:
CREATE FULLTEXT CATALOG FTC_Test
AS DEFAULT
AUTHORIZATION dbo
CREATE FULLTEXT INDEX ON guest.FtsTest(FullName)
KEY INDEX PK_FtsTest ON FTC_Test
I wait briefly and then check to see if the index has been populated:
SELECT * FROM sys.fulltext_indexes
crawl_end_date is not null, so I'm assuming I don't have to wait anymore before I try some FTS searches. Right? I can't get any queries to return anything, though.
The following tells me the full text item count for the table is zero:
DECLARE @TableId INT
SELECT @TableId = id FROM sys.sysobjects WHERE [Name] = 'FtsTest'
SELECT OBJECTPROPERTYEX(@TableId, 'TableFulltextItemCount') AS TableFulltextItemCount
As mentioned, the full text search works on my test server. Both of them are SQL 20012 SP1 (11.0.3000) x64 running on WinServer 2008 R2 SP1.
January 29, 2014 at 1:09 pm
Here's some additional info culled from a log file:
2014-01-29 14:48:14.02 spid22s Informational: Full-text Full population initialized for table or indexed view '[MyDB].
.[FtsTest]' (table or indexed view ID '725121874', database ID '5'). Population sub-tasks: 4.
2014-01-29 14:48:16.05 spid40s Error '0x80004005' occurred during full-text index population for table or indexed view '[MyDB].
.[FtsTest]' (table or indexed view ID '725121874', database ID '5'), full-text key value '1000'. Attempt will be made to reindex it.
2014-01-29 14:48:16.05 spid40s Error '0x80004005' occurred during full-text index population for table or indexed view '[MyDB].
.[FtsTest]' (table or indexed view ID '725121874', database ID '5'), full-text key value '1001'. Attempt will be made to reindex it.
2014-01-29 14:48:16.05 spid40s Error '0x80004005' occurred during full-text index population for table or indexed view '[MyDB].
.[FtsTest]' (table or indexed view ID '725121874', database ID '5'), full-text key value '1002'. Attempt will be made to reindex it.
2014-01-29 14:48:16.50 spid87 Informational: Full-text Full population completed for table or indexed view '[MyDB].
.[FtsTest]' (table or indexed view ID '725121874', database ID '5'). Number of documents processed: 3. Number of documents failed: 3. Number of documents that will be retried: 3.
January 31, 2014 at 10:17 am
Geez, there really isn't much info out there that is directly related to my issue. I did stumble upon this MS support article about the full-text indexing time-out value: Error 0x80040e97 occurs when you use integrated full-text search in SQL Server
It didn't seem to pertain to my situation. However, I ran this on both my test server and prod server: sp_fulltext_service 'ft_timeout'
The setting values were different: 60000 in test, 0 in prod. I changed the setting in prod to match the setting in test as follows: EXEC sp_fulltext_service 'ft_timeout', 60000
After rebuilding the full-text indexes, everything seems normal. CONTAINS and FREETEXT queries are working as expected.
July 15, 2015 at 12:50 pm
Thank you for posting this solution!
I was having an issue after migrating a database to a new server. After migration the FT indexes worked fine. However, they would break anytime there was an attempt to repopulate the FT indexes. The repopulate attempt would repopulate the index with 0 records. This was apparently due to the ft_timeout being set to 0 on the new server. No clue how or when it got set to 0 but it was.
Updated it to 60000 and everything resumed normal operation.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply