Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Full Text Search Expand / Collapse
Author
Message
Posted Wednesday, January 29, 2014 12:52 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, March 28, 2014 7:43 AM
Points: 356, Visits: 473
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
Orange County, FL
Post #1536082
Posted Wednesday, January 29, 2014 1:09 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, March 28, 2014 7:43 AM
Points: 356, Visits: 473
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
Orange County, FL
Post #1536089
Posted Friday, January 31, 2014 10:17 AM This worked for the OP Answer marked as solution


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, March 28, 2014 7:43 AM
Points: 356, Visits: 473
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
Orange County, FL
Post #1536880
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse