Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Full Text Search


Full Text Search

Author
Message
Dave Mason
Dave Mason
SSC-Addicted
SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)

Group: General Forum Members
Points: 430 Visits: 872
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.

Dave Mason
Seminole County, FL

Dave Mason
Dave Mason
SSC-Addicted
SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)

Group: General Forum Members
Points: 430 Visits: 872
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].[guest].[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].[guest].[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].[guest].[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].[guest].[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].[guest].[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.


Dave Mason
Seminole County, FL

Dave Mason
Dave Mason
SSC-Addicted
SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)

Group: General Forum Members
Points: 430 Visits: 872
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.

Dave Mason
Seminole County, FL

RunSQL
RunSQL
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 103
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search