Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 200
  • Last Modified:

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
0
MediaBanc
Asked:
MediaBanc
  • 6
  • 5
2 Solutions
 
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
 
MediaBancAuthor Commented:
I also installed this service pack SQLServer2008R2SP2-KB2630458-x64-ENU
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Anthony PerkinsCommented:
Then I am afraid I have no idea.
0
 
Anthony PerkinsCommented:
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:
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
 
MediaBancAuthor 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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now