Solved

Full-Text not working!

Posted on 2003-10-29
40
999 Views
Last Modified: 2007-12-19
Hello everyone,
I've got a little problem with the Full-Text Search (MSSearch).

If I try to create a ft-catalog trough Enterprise Manager, nothing happens - it populates and populates - no indexes moving - no logfile increasing - nor the harddiscspace (off the ft) getting occupied.

Here's the history what I've done:
- Changed user of MSSearch to SQL User (not system user)
- Changed 'Accounts' registry key to 'Administratoren' (Administrators) does not exist, running a german system
        -- HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\Accounts
- Changed MSSQL Account (to same Account) and restarted

Now the problem is...
- I've got an empty catalog in the system wich i cannot delete (enterprise manager timeout - left it running for 30 minutes)
- I cannot do a sp_fulltext_database 'enable' (wich would reset the data) -> times out also (left it running for 5 hours)
- I cannot stop the MSSearch engine (probably because the hanged sp_fulltext proc)
- i cannot delete the catalog because ft-feature is not enabled (sp_fulltext_database...)
  - UPDATE: also cannot stop it after reboot, even if  the ft-cat itself shows IDLE and no processes are accessing it
- cannot run sp_fulltext_database 'disable' - same timeout (running now since 70 mins...)

Does anyone have any idea how to fix this?
System Information:
- Win 2000 - German
- Microsoft SQL Server 2000 Service Pack 3

Gather Logs:
- no errors
Event Logs:
- no errors
0
Comment
Question by:kauf
  • 25
  • 10
  • 4
40 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9647168
Perhaps it is time to re-install SQL Server and perhaps the O/S too.  It might be quicker and cleaner than finding the solution.  That has been my experience, with a similar problem some months ago.

Anthony
0
 

Author Comment

by:kauf
ID: 9648719
Currently i'm trying to do it w/o a reinstall, i think with the reinstall there could occur more problems with the running system...
0
 
LVL 1

Expert Comment

by:RadimHampel
ID: 9649270
How many records are in your table? Try to stop ms-search and delete ft calalog from disk(mssql\ftdata)

Radim
0
 

Author Comment

by:kauf
ID: 9649527
Hi Radim,
I couldnt stop the MSSearch but I've set it to manual start and rebooted.
After the reboot i've renamed the FT-Folder and the service started fine.
But if i re-sp_fulltext_database 'enable' on the database it seems to start indexing again - even creates the paths again (even if enterprise manager stats idle on catalog)

all the gthr-logs in the ft-dir are empty (65kb but cscript... gives out nothing)
in the gatherlogs dir ive got two cats-logs (strangewise)...
SQL0001000005.4.gthr shows
30.10.2003 00:05:18 ADD The gatherer has started
30.10.2003 13:37:36 ADD The gatherer has started
30.10.2003 13:37:36 ADD The initialization has completed

SQL0001000006.5.gthr shows
30.10.2003 00:05:18             Add     The gatherer has started
30.10.2003 00:05:18             Add     The initialization has completed


mssearch takes currently 25% of system usage (4 processors), and the sp_fulltext_database 'enable' is still running in query analyzer (since 9 minutes)
0
 

Author Comment

by:kauf
ID: 9651394
forgot the recordcount:
- ~1830000 records
- 2 fields should be indexed -> field one varchar(150) / field two varchar(255)
0
 

Author Comment

by:kauf
ID: 9651637
today i've tried to:
- rename main folder(ft-folder)
- delete registry entries (all SQL000... projects)
- restart server
- sp_fulltext_database 'enable'
still the same problem, it re-creates the main search catalog

does anybody know how long the upper ft-search would normally take to full-populate?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9652129
For 2 million rows I would allow up to an hour for a full population.

Anthony
0
 

Author Comment

by:kauf
ID: 9652224
last time i waited over 4 days before i cancled :)

i've enabled named pipes, because i've read that this could be a problem...
sysinternals.com'filemon shows me thousands of open requests from mssearch but nothing else... (tried to delete the ft-catalog from enterprise manager)...
i'm still waiting for the deletion to complete...
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9652272
Have you ensured that the MS Search account has permissions to write (the full-text search catalog) to the server?

Anthony
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9652293
That was not very clear.  I was referring to the account set up for the MS Search service.

Anthony
0
 

Author Comment

by:kauf
ID: 9652423
security like this:
 - group administratoren (changed in the NTAdminGroup registry-key)
 - NT-admin-user 'srvadm', also is user under SRV01\srvadm with sysadmin / db_owner to all db's in sql

both sql, and mssearch are running under srvadm

but it still seems to me now, that mssearch cannot open a connection.
because in the file monitor of sysinternals.com, i just see create requests, nothing else (and i mean thousands of them per minute)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9654786
I am out of ideas.

Anthony
0
 
LVL 1

Expert Comment

by:RadimHampel
ID: 9655998
2 millions of rows if quite big number.

But did you try to reinstall mssearch? It should be action, that takes few minutes and then you see the results.
0
 

Author Comment

by:kauf
ID: 9656170
how can i re-install mssearch, i didn't see an option to uninstall (except full sql uninstall)?

is it enough when i delete the mssearch registry entry (software\microsoft\mssearch\...)
0
 
LVL 1

Expert Comment

by:RadimHampel
ID: 9656201
Put in sql serv cd, choose to update installed components/update installation(don't know exactly) and uncheck ft search choice. After uninstall, repeat and include search again.
0
 

Author Comment

by:kauf
ID: 9656348
it seems that's not possible, i just can add new items or remove the complete installation...
Trough the system software control-panel i also just can remove complete installation
0
 
LVL 1

Expert Comment

by:RadimHampel
ID: 9656425
There is a way to reinstall mssearch. You choose to update your installation, and, then you have checkbox with mssearch disabled? You cant uninstall it?
0
 

Author Comment

by:kauf
ID: 9656567
I could install it afterwards - yes, but the checkbox is already checked, so the software is installed.
I didnt find a way to uninstall it...
0
 

Author Comment

by:kauf
ID: 9656628
I've got that far that i was able to delete the original ft-catalog (and all entries in enterprise manager).
Now id like to create a temporary - small - catalog to check if all works...
but it hangs on following command (generated by ft-wizard):
if not exists (select * from dbo.sysfulltextcatalogs where name = NTempCat')
exec sp_fulltext_catalog N'TempCat', N'create' , N'F:\FT_Folder'

I've killed the process after waiting 8 hours (overnight), but it seems stuck (killed/rollback)...
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9658244
I suspect that eventually you will come to the same conclusion that we did some months back:  It is better to cut you losses and rebuild the entire box, including the O/S and SQL Server.  In the days that you have been fighting this, you could have accomplished at least 4 rebuilds.

Anthony
0
 

Author Comment

by:kauf
ID: 9658572
Anthony:
That's true, but fact is - i hate to give up on such stuff :-).
And due the fact that the FT-Feature currently is not a real MUST, i'd prior try to solve it this way...
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9658617
Fair enough.  I hope you find the reason.  Perhaps you can post it here when you do.

Anthony
0
 

Author Comment

by:kauf
ID: 9661603
It seems i finally got it running, but i have to say - it was the one solution i didn't want to take :-).
I had to change both - MSSQL User & MSSearch User back to LocalSystem - now everything works fine...
0
 

Author Comment

by:kauf
ID: 9670720
AGHH :) now i'm really getting crazy...
The FT works fine, catalog populated - search works...
Now the 'lsass.exe' proc takes up to 100% cpu (all 4 cpus!!) and I don't know why, if i stop mssearch it works fine....
if i start it up again it recovers the ft-cats and then peaks again...

details:
lsass.exe - 80% cpu
msdmn.exe (or something like that) also pretty much cpu utilisation

anyone an idea?
0
 

Author Comment

by:kauf
ID: 9670857
sorry, mssdmn.exe not msdmn.exe
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9671219
Take a look at these articles from MSDN:

Lsass.exe Spikes at 100 Percent CPU Usage and Then Shows a Typical Load for 60 Minutes Before Spiking Again
http://support.microsoft.com/default.aspx?scid=kb;en-us;811172

High CPU and Memory Utilization When You Add Objects to or Remove Objects From the Active Directory
http://support.microsoft.com/default.aspx?scid=kb;en-us;315697

CPU Usage in Lsass.exe Increases When You Run netDeploy
http://support.microsoft.com/default.aspx?scid=kb;en-us;315980

Anthony
0
 

Author Comment

by:kauf
ID: 9671450
Dont seem to find a resolution there, i've only got one administrators members, and that's the accoun't i'm logging in.
NetDeploy is a third party software - right? (don't have it installed).
The SQL server is the only pc in his workgroup, no other clients directly in the workgroup...
0
 

Author Comment

by:kauf
ID: 9674763
It seems like an user-error to me again, if i start the mssearch service, everything works fine.
But as soon as he recovered the ft-catalog (mssearch cpu falls down do normal usage) lsass.exe & mssdmn.exe are peaking (moslty lsass.exe -> up to 100%).
As soon as i stop mssearch again, the cpu's return to normal - same game if i restart mssearch.

I've found several newsposts and forums concerning this problem (not exact the same, mostly in combination with exchange server or as posted above), but i didnt find any usable solution.
0
 

Author Comment

by:kauf
ID: 9674775
Before i forget - i've reinstalled SP4 (win2k) today, no change.
SQL 2k has SP3 installed.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9675039
Again I am out of ideas.  I hope you find a solution.

Anthony
0
 

Author Comment

by:kauf
ID: 9682954
i think its a problem of mssdmn.exe and user rights, i just cant get behind it.
if enabled all login features in the local security sets but didn't get any unsuccessfull logon attempt of a service (LSASS and MSSDMN log on successfully as far as i see)...
Does anybody know how i could monitor this more directly?

Ah yes, i had some errors in mssearch log (filemon) that it couldnt find security.dll / and 2 other dlls - they're normal system dlls (winnt/system32) so i copied them into the mssearch folder - but didn't solve anything.

Does anybody know how i could monitor those services more detailed?
0
 

Author Comment

by:kauf
ID: 9693101
i've deleted and recreated the catalog, same thing.
I think its a  problem with the incremental update (even if it runs fine after i try right after the population).

I've got a timestamp field, so there should be no problem...
Does anyone know where i can check the change_tracking state of this table?
0
 

Author Comment

by:kauf
ID: 9693162
It seems like if i do an incremental on the catalog it fails.
I've stopped change tracking (does it need this when i have a timestamp field?) and startet:
sp_fulltext_table 'tblSearch','start_incremental'

and as far as now (~3 minutes) no LSASS peaking...
0
 

Author Comment

by:kauf
ID: 9693188
It peaked again after ~10 Minutes, so catalog incremental and table incremental don't work...
0
 

Author Comment

by:kauf
ID: 9693237
start_change_tracking also shows the same peaks...
0
 

Author Comment

by:kauf
ID: 9693289
i've created a smaller table (structure same as above) to test it in shorter time, lsass.exe also peaks - but there i can finish the process.
the problem is on the big table it seems to take very very long, and if lsass peaks - bigger queries from other users are blocked...
0
 

Author Comment

by:kauf
ID: 9700213
if it helps anything, it seems like the 'select' for mssearch works w/o problems (where it gets the changed data) - as soon as it starts updating, lsass will peak..
in sysinternals's tokemon i see thousands of impersonating requests (but no errors)...
0
 

Accepted Solution

by:
kauf earned 0 total points
ID: 9729863
Seems like i fixed it.
What  i've done:
 - rename the main admin user back to the root-setting (administrator)
 - re-install sp3a (sql)

now incremental population works fine, withouth lsass.exe peaking! :-)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9732222
I am glad you got it figured out.  I suggest you post a message in Community Support to have this question PAQ'd and your points refunded.  See here for more info:

I answered my question myself. What do I do?
http://www.experts-exchange.com/help/closing.jsp#4

Anthony
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

757 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

22 Experts available now in Live!

Get 1:1 Help Now