Solved

SQL Server 2008 R2 Full-Text Index Hanging

Posted on 2014-02-10
13
116 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
  • 6
  • 5
13 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
Comment Utility
Hello Anthony,

I'm using 10.50.4000.0
0
 

Author Comment

by:MediaBanc
Comment Utility
I also installed this service pack SQLServer2008R2SP2-KB2630458-x64-ENU
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Then I am afraid I have no idea.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
Comment Utility
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
Comment Utility
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:MediaBanc
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now