MediaBanc
asked on
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].[Arti
2014-02-11 04:22:27.38 spid38s Informational: Full-text Auto population completed for table or indexed view '[Indexing_ph].[dbo].[Arti
2014-02-11 04:23:16.16 spid38s Informational: Full-text Auto population initialized for table or indexed view '[Indexing_ph].[dbo].[Arti
2014-02-11 04:23:29.33 spid36s Informational: Full-text Auto population completed for table or indexed view '[Indexing_ph].[dbo].[Arti
2014-02-11 04:24:16.14 spid36s Informational: Full-text Auto population initialized for table or indexed view '[Indexing_ph].[dbo].[Arti
2014-02-11 04:24:18.73 spid39s Informational: Full-text Auto population completed for table or indexed view '[Indexing_ph].[dbo].[Arti
2014-02-11 04:25:13.05 spid27s Informational: Full-text Auto population initialized for table or indexed view '[Indexing_ph].[dbo].[Arti
2014-02-11 04:27:17.96 spid29s Informational: Full-text Auto population completed for table or indexed view '[Indexing_ph].[dbo].[Arti
2014-02-11 04:38:28.58 spid29s Informational: Full-text Auto population initialized for table or indexed view '[Indexing_ph].[dbo].[Arti
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].[Arti
2014-02-11 05:02:20.19 spid32s Informational: Full-text Manual population completed for table or indexed view '[Indexing_ph].[dbo].[Arti
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].[Arti
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].[Arti
2014-02-11 05:02:21.68 spid29s Informational: Full-text Auto population initialized for table or indexed view '[Indexing_ph].[dbo].[Arti
2014-02-11 05:02:22.69 spid29s Informational: Full-text Auto population completed for table or indexed view '[Indexing_ph].[dbo].[Arti
2014-02-11 05:03:15.61 spid24s Informational: Full-text Auto population initialized for table or indexed view '[Indexing_ph].[dbo].[Arti
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
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].[Arti
2014-02-11 04:22:27.38 spid38s Informational: Full-text Auto population completed for table or indexed view '[Indexing_ph].[dbo].[Arti
2014-02-11 04:23:16.16 spid38s Informational: Full-text Auto population initialized for table or indexed view '[Indexing_ph].[dbo].[Arti
2014-02-11 04:23:29.33 spid36s Informational: Full-text Auto population completed for table or indexed view '[Indexing_ph].[dbo].[Arti
2014-02-11 04:24:16.14 spid36s Informational: Full-text Auto population initialized for table or indexed view '[Indexing_ph].[dbo].[Arti
2014-02-11 04:24:18.73 spid39s Informational: Full-text Auto population completed for table or indexed view '[Indexing_ph].[dbo].[Arti
2014-02-11 04:25:13.05 spid27s Informational: Full-text Auto population initialized for table or indexed view '[Indexing_ph].[dbo].[Arti
2014-02-11 04:27:17.96 spid29s Informational: Full-text Auto population completed for table or indexed view '[Indexing_ph].[dbo].[Arti
2014-02-11 04:38:28.58 spid29s Informational: Full-text Auto population initialized for table or indexed view '[Indexing_ph].[dbo].[Arti
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].[Arti
2014-02-11 05:02:20.19 spid32s Informational: Full-text Manual population completed for table or indexed view '[Indexing_ph].[dbo].[Arti
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].[Arti
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].[Arti
2014-02-11 05:02:21.68 spid29s Informational: Full-text Auto population initialized for table or indexed view '[Indexing_ph].[dbo].[Arti
2014-02-11 05:02:22.69 spid29s Informational: Full-text Auto population completed for table or indexed view '[Indexing_ph].[dbo].[Arti
2014-02-11 05:03:15.61 spid24s Informational: Full-text Auto population initialized for table or indexed view '[Indexing_ph].[dbo].[Arti
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
ASKER
Hello Anthony,
I'm using 10.50.4000.0
I'm using 10.50.4000.0
ASKER
I also installed this service pack SQLServer2008R2SP2-KB26304
Then I am afraid I have no idea.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
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.
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.
ASKER
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.
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
SELECT SERVERPROPERTY('ProductVer