SQL Server 2008 R2 Full-Text Index Hanging

Hi Experts!

I have this scenario regarding our system and I'm spotting a problem with Full-Text Index:

Table Name: article_content (with Full-Text Index auto population)
Service Name 1: TXT2DB Mover service (adhoc query that get data from .txt file and insert to article_content)
Service Name 2: Brand KeywordMatching (keywordmatching service)
System Process: whenever TXT2DB Mover service puts data to article_content table, Full-Text population should auto populate. Brand KeywordMatching will only work if population is working properly.

Problem: Brand Keywordmatching is not working.

1st workaround solution and result: Restart Brand Keywordmatching service. After restarting the Brand Keywordmatching, the result is still not working.

2nd workaround solution and result: Restart SQL Server Service. After restart SQL Server, Brand Keywordmatching is working.

SQLFT Log: I got this information during the time Full-Text is working properly. And I noticed that during 2014-02-11 04:38:28.58, I didn't see any information for completed population.
2014-02-11 04:22:21.96 spid38s     Informational: Full-text Auto population initialized for table or indexed view '[Indexing_ph].[dbo].[Article_Content]' (table or indexed view ID '1890105774', database ID '5'). Population sub-tasks: 1.
2014-02-11 04:22:27.38 spid38s     Informational: Full-text Auto population completed for table or indexed view '[Indexing_ph].[dbo].[Article_Content]' (table or indexed view ID '1890105774', database ID '5'). Number of documents processed: 6. Number of documents failed: 0. Number of documents that will be retried: 0.
2014-02-11 04:23:16.16 spid38s     Informational: Full-text Auto population initialized for table or indexed view '[Indexing_ph].[dbo].[Article_Content]' (table or indexed view ID '1890105774', database ID '5'). Population sub-tasks: 1.
2014-02-11 04:23:29.33 spid36s     Informational: Full-text Auto population completed for table or indexed view '[Indexing_ph].[dbo].[Article_Content]' (table or indexed view ID '1890105774', database ID '5'). Number of documents processed: 4. Number of documents failed: 0. Number of documents that will be retried: 0.
2014-02-11 04:24:16.14 spid36s     Informational: Full-text Auto population initialized for table or indexed view '[Indexing_ph].[dbo].[Article_Content]' (table or indexed view ID '1890105774', database ID '5'). Population sub-tasks: 1.
2014-02-11 04:24:18.73 spid39s     Informational: Full-text Auto population completed for table or indexed view '[Indexing_ph].[dbo].[Article_Content]' (table or indexed view ID '1890105774', database ID '5'). Number of documents processed: 3. Number of documents failed: 0. Number of documents that will be retried: 0.
2014-02-11 04:25:13.05 spid27s     Informational: Full-text Auto population initialized for table or indexed view '[Indexing_ph].[dbo].[Article_Content]' (table or indexed view ID '1890105774', database ID '5'). Population sub-tasks: 1.
2014-02-11 04:27:17.96 spid29s     Informational: Full-text Auto population completed for table or indexed view '[Indexing_ph].[dbo].[Article_Content]' (table or indexed view ID '1890105774', database ID '5'). Number of documents processed: 6. Number of documents failed: 0. Number of documents that will be retried: 0.
2014-02-11 04:38:28.58 spid29s     Informational: Full-text Auto population initialized for table or indexed view '[Indexing_ph].[dbo].[Article_Content]' (table or indexed view ID '1890105774', database ID '5'). Population sub-tasks: 1.


After restarting the SQL Server Service, here is the log:

2014-02-11 05:02:17.81 spid32s     Informational: Full-text Manual population initialized for table or indexed view '[Indexing_ph].[dbo].[Article_Content]' (table or indexed view ID '1890105774', database ID '5'). Population sub-tasks: 1.
2014-02-11 05:02:20.19 spid32s     Informational: Full-text Manual population completed for table or indexed view '[Indexing_ph].[dbo].[Article_Content]' (table or indexed view ID '1890105774', database ID '5'). Number of documents processed: 191. Number of documents failed: 0. Number of documents that will be retried: 0.
2014-02-11 05:02:20.43 spid35s     A full-text retry pass of Manual population started for table or indexed view '[Indexing_ph].[dbo].[Article_Content]'. Table or indexed view ID is '1890105774'. Database ID is '5'.
2014-02-11 05:02:21.20 spid32s     Informational: Full-text retry pass of Manual population completed for table or indexed view '[Indexing_ph].[dbo].[Article_Content]' (table or indexed view ID '1890105774', database ID '5'). Number of retry documents processed: 0. Number of documents failed: 0.
2014-02-11 05:02:21.68 spid29s     Informational: Full-text Auto population initialized for table or indexed view '[Indexing_ph].[dbo].[Article_Content]' (table or indexed view ID '1890105774', database ID '5'). Population sub-tasks: 1.
2014-02-11 05:02:22.69 spid29s     Informational: Full-text Auto population completed for table or indexed view '[Indexing_ph].[dbo].[Article_Content]' (table or indexed view ID '1890105774', database ID '5'). Number of documents processed: 5. Number of documents failed: 0. Number of documents that will be retried: 0.
2014-02-11 05:03:15.61 spid24s     Informational: Full-text Auto population initialized for table or indexed view '[Indexing_ph].[dbo].[Article_Content]' (table or indexed view ID '1890105774', database ID '5'). Population sub-tasks: 1.


This log made me think that Full-Text Index is hanging. I also attached the log files.

Any experience about this?

Thank you.

Jin
SQLFT0000500005.LOG
SQLFT0000500005.LOG.1.txt
MediaBancAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Anthony PerkinsConnect With a Mentor Commented:
Actually I misread what you posted, it is Cumulative Update 3 that you need (v. 10.50.4266.0) according to this article:
FIX: You receive error messages when you perform a population for a large full-text index in SQL Server 2008 or in SQL Server 2008 R2
0
 
Anthony PerkinsCommented:
Which specific version are you on?  In other words, I know you said you are using SQL Server 2008-R2, now what service pack and Cumulative Updates have you applied if any?

SELECT  SERVERPROPERTY('ProductVersion')
0
 
MediaBancAuthor Commented:
Hello Anthony,

I'm using 10.50.4000.0
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
MediaBancAuthor Commented:
I also installed this service pack SQLServer2008R2SP2-KB2630458-x64-ENU
0
 
Anthony PerkinsCommented:
Then I am afraid I have no idea.
0
 
Anthony PerkinsCommented:
For what it is worth we had Symptom 2 from that article last week.  We attempted the workarounds without any success, but have not applied the CU yet.
0
 
MediaBancAuthor Commented:
Thanks Anthony,

I will try first to update then monitor for a couple of days if working fine. I will post a reply as soon as it still get problem.
0
 
MediaBancConnect With a Mentor Author Commented:
Hi Anthony,

Seems like the CU did not work. As of now, what I did was I changed Change Tracking to Manual and put this script in SQL Server Agent job to run every 15 seconds.

ALTER FULLTEXT INDEX ON dbo.Article_Content
START UPDATE POPULATION;
GO

Open in new window


After that solution, everything is fine.
0
 
MediaBancAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for MediaBanc's comment #a39851837
Assisted answer: 250 points for acperkins's comment #a39849077

for the following reason:

It didn't have any problem after doing the solution.
0
 
Anthony PerkinsCommented:
Seems like the CU did not work.
That is good to know.

As of now, what I did was I changed Change Tracking to Manual and put this script in SQL Server Agent job to run every 15 seconds.
I believe you can also schedule it.  But I suppose that is the same thing your are doing.
0
 
MediaBancAuthor Commented:
Now, I need to dig deeper on how to check for the reason on why Full-Text index is hanging when using automatic change tracking.
0
 
Moussa MokhtariEnterpreneurCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
All Courses

From novice to tech pro — start learning today.