Solved

SQL Server 2008 R2 Full-Text Index Hanging

Posted on 2014-02-10
13
176 Views
Last Modified: 2016-07-21
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
0
Comment
Question by:MediaBanc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
13 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39849030
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
 

Author Comment

by:MediaBanc
ID: 39849041
Hello Anthony,

I'm using 10.50.4000.0
0
 

Author Comment

by:MediaBanc
ID: 39849046
I also installed this service pack SQLServer2008R2SP2-KB2630458-x64-ENU
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39849068
Then I am afraid I have no idea.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 39849077
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39849080
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
 

Author Comment

by:MediaBanc
ID: 39849087
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
 

Assisted Solution

by:MediaBanc
MediaBanc earned 0 total points
ID: 39851837
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
 

Author Comment

by:MediaBanc
ID: 39852192
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39852090
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
 

Author Comment

by:MediaBanc
ID: 39852191
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
 
LVL 9

Expert Comment

by:Moussa Mokhtari
ID: 41722314
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question