 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / Other Technologies / Full-Text Search / December 2004Population running for extended period
I have a job the runs nightly that does a brute force "exec sp_fulltext_catalog @FtcName, 'start incremental'" on all customer databases and all FTC. I noticed that for a few days in a row that in specific databases there are several FTC that always have a status of either "incremental population in progress" or "Population in progress". I disabled the job and noticed that the Last Population Date. I noticed that the Last Population Date on some of the FTC are changing, but for some very high volume FTC (I would hope) the Last Population Date has not changed in *10 days*. Server Info: - Clustered SQL Server 2000 - FTC are not Change Tracking enabled. - Both status occur on tables that have 46,000 to 49,000 rows (small) (spot checked). - There are 226 FTC in 45 databases. - SQL Server Version: Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4) Any help would be greatly appreciated. -Jim Roberts there is a problem with SQL FTS and change tracking in a clustered environment. The catalogs seem to go into read only mode after you fail them over. Check the gatherer logs to see if it reports errors. Also run settemppath to place your temporary MSSearch indexing files on one of the shared drives.  SignatureHilary Cotter Looking for a SQL Server replication book? Now available for purchase at: http://www.nwsu.com/0974973602.html >I have a job the runs nightly that does a brute force "exec > sp_fulltext_catalog @FtcName, 'start incremental'" on all customer [quoted text clipped - 23 lines] > > -Jim Roberts Jim, First of all, thank you for providing the @@version info and you should know that SQL Server 2000 Full-Text Search (FTS) components are fully supported in a clustered (MSCS) environment. This is confirmed via several entries in the SQL Server 2000 BOL such as "Using SQL Server Tools with Failover Clustering", "Creating a Failover Cluster", and "Failover Cluster Troubleshooting". I'd recommend that you review your active node's Application event log for any "Microsoft Search" or MssCi (especially, MssCi) as this will indicate what is causing the apparent hanging of the FT Incremental Populations. Be sure to review all errors, warnings and error messages and specifically look for "80070002 - The system cannot find the file specified" as this is a common symptom of FT Indexing problems in clustered environments. Specifically, please review the two following KB articles: Q304282 PRB: Full Text Resource Fails to Come Online on a Cluster with "Event ID 1069 : SQL Cluster Resource 'Full Text' failed" Error Message http://support.microsoft.com/default.aspx?scid=kb;en-us;304282&sd=tech 812666 How to recover a failed full-text search resource on a clustered instance of SQL Server 2000 http://support.microsoft.com/default.aspx?kbid=812666 Hope this helps! John > I have a job the runs nightly that does a brute force "exec > sp_fulltext_catalog @FtcName, 'start incremental'" on all customer [quoted text clipped - 23 lines] > > -Jim Roberts I checked the Application Log and found 0 errors from for either Mssearch or MssCi in the past month. I started working down the road of a resource issue as you indicated in your comment. I'm not sure that these issues have anything to do with my problem but it certainly sounds like a place to start. I had noticed two things: - Per another article from you, I started looking at the possibility that we may have run out of disk space. You had told someone that there should be at least 15% free space on a drive that has FTC files. There was one drive that had 16%. This reminded me of something that occurred on the weekend when this started happening. I had rebuilt a large number (145 of 225) of FTC. I looked back in our monitoring and found that, indeed on that day, we had run to within 2% of our total disk space for that drive (monitoring has been adjusted :-). Is it possible that this could have caused the FTC to "hang". And if so will simply restarting FTC service fix this issue? - My assumption that *none* of the FTC were using Change Tracking was wrong. I've found 20 (so far) that are enabled for Change Tracking. Is it a true statement that if an FTC is enabled for Change Tracking (and after the full population) the indexes will have a status of "Incremental population in progress" always? One additional question (may be off topic): We're considering moving to enabling Change Tracking for all FTC. Is there a good reason not to? -Jim Jim, Ok, I guess it is possible that the "Microsoft Search" and MssCi rolled off the current App event log and are not available. Yes, I've said in past postings that there should always be at least 15% free disk space on the drive where the FT Catalog files reside (it's good to see that you've done your homework! ;-). Yes, this is a possible cause of the FTC hanging, and assuming no other issues, re-starting the MSSearch service should cause the Population to re-start automatically from the point in time where it stopped. Although, it may take some time to get to that point. Yes, it is a true statement that an FTC is enabled for Change Tracking (and after the full population) the indexes will have a status of "Incremental population in progress", but not necessarily at all times. See, SQL 2000 BOL title "sp_help_fulltext_catalogs" for "6 = Incremental population in progress" as well as possible status: "7 = Building index " and "9 = Change tracking". As for moving all of your FT Catalogs to CT with UIiB, for the most part this is a good idea, as it will keep your FT Catalogs fresh and therefore your FTS queries will be current. However, there are concerns when doing massive updates (or deletes), i.e., >50% of the table's data changes, as the Update Index in Background cannot keep up with this level of change. Another factor is that depending upon the amount of data changes (say 20% to 50%), the data in the FT Catalog may not be current until a Master Merge is done as this done normally after a Full or Incremental population as well as at Midnight (controllable via a registry key). Other factors include your server's resources, see SQL 2000 BOL title "Maintaining Full-Text Indexes" for more details. Hope this helps! John > I checked the Application Log and found 0 errors from for either > Mssearch or MssCi in the past month. [quoted text clipped - 25 lines] > > -Jim Hi John, Sorry so long on the reply (holidays and all ;). I had freed up some space on two of the drives that were danger-close (less then 15% freespace). Unfortunately the space that I freed up was rapidly consumed. Obviously I will need to move the FTC on these drives to other drives. So now my question now is that if Mssearch hangs incrementally populating indexes on a drive that has less then 15% free, is it possible that other FTC on drives that have plenty of space (50% or more free space) will also hang? This is the scenario that I see. My concern is that there is some other condition causing this and after I move the the FTC from the drives that are low, that Mssearch will still be hanging on the FTC on the drives that have plenty of space. -Jim Roberts Not to worry, Jim! Have you been able to find any related & new Application source events for "Microsoft Search" and MssCi ? This would help indicate the true reason (vs. guessing that it's disk drive space related) for the incremental hanging of the MSSearch service. If you do find any related warnings or errors from MssCi, please post them here. The 15% free disk space relates to the disk drive where the FT Catalog exists, but MSSearch can also be affected by low disk drive on the system drive (usually the c:\ disk drive) as temporary gatherer files are written there as well as the disk drive the SQL Server FDATA folder exists as the Gather log files are written there. If possible, check to see if there are any more new "Microsoft Search" and MssCi source events. Thanks, John SignatureSQL Full Text Search Blog http://spaces.msn.com/members/jtkane/ > Hi John, > [quoted text clipped - 16 lines] > > -Jim Roberts Ah! <sheepishly> I hadn't checked the Event logs this time and previously, I had filtered only on "Error" </sheepishly>. After applying a filter for Warnings in addition to Errors I see a ton of the following warnings: The Gatherer property mapping file cannot be opened. Error: 80070002. The default values are being used. You may have to copy the property mapping file from the setup CD, or reinstall the application. And this time there is *one* error: An error occurred in the resource status monitor for instance <SQL Server Fulltext (CLHC04)>: 800706be - The remote procedure call failed. As a matter of fact, I just noticed, that the last message logged from Microsoft Search was on 12/18 and there *NO* error or warning events from MssCi (the last information event was logged on 12/19). That warning message is a little troubling, but I have no idea what to look for, when looking for a "property mapping" file. And the error message is nearly useless with out some additional context. One additional fact; the service is setup to run on 1 of 4 processors (2 of 8 hyperthreaded) via imagecfg.exe. I know that this will cause MsSearch to take longer in it's processing, but is it possible that this is causing some sort of race condition when combined with low drive space? As for the system drive; it has 11GB free (64%). I know that I need to move the catalogs on the short drives so I've scheduled that for this weekend. Hopefully, this will provide some relief. -Jim Jim, Not to worry, as that's why I asked for all Application Event source event (especially, as MssCi) be reviewed! <G> The "property map" file is Gathrprm.Txt in the \FTDATA\SQLServer\Config folder where you have SQL Server 2000 installed. If the file is present, then it may be that a Registry key &/or value that points to this file may not be present or set correctly. No, this is no related to some sort of race condition or even low drive space, as it is specific to the above missing file &/or incorrectly configured registry key/value pair. It is possible that during failover that the files &/or registry setting (both are transferred via binary files during failover) did not succeed sometime in the past. Furthermore, this is related to error 80070002 (The system cannot find the file specified.), but recorded with more detail as "The Gatherer property mapping file cannot be opened. Error: 80070002". I'd recommend that you copy the property mapping file (Gathrprm.Txt) from your SQL Server 2000 setup CD, or reinstall the MSSearch components via the procedures in KB article: 812666 "How to recover a failed full-text search resource on a clustered instance of SQL Server 2000" at http://support.microsoft.com/default.aspx?kbid=812666 If you decide not to re-install the MSSearch components via the above documented procedures, you can do the following: 1) Copy the FTDATA config directory (\FTDATA\SQLServer\*.*) from another SQL instance, and take the instance offline/online. NOTE: The FTDATA config (\FTDATA\SQLServer\Config) directory is under the SQL install path, so it should be on the shared disk. Most likely it is the registry key that has an incorrect value. 2) Using Regedt32, edit the following Register key and value: HKLM\Software\Microsoft\Search\1.0\Gathering Manager\Applications\SQLServer (REG_SZ) ApplicationPath <ApplicationPath>\Config\gatherprm.txt NOTE: After making this Registry change, you should failover. There are additional files that MSSearch requires in this folder. Additionally, you may have to grant the Cluster Service account explicit Full Control to the above ApplicationPath's FTDATA folder. Regards, John SignatureSQL Full Text Search Blog http://spaces.msn.com/members/jtkane/ > Ah! <sheepishly> I hadn't checked the Event logs this time and > previously, I had filtered only on "Error" </sheepishly>. [quoted text clipped - 30 lines] > > -Jim |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.