Hi,

For the past two weeks, we've been having MAJOR problems with the full
text features of SQL 2005 on one particular server which is resulting in
the hosted websites going offline at random intervals. These problems
include (in approx order of how often they occur):

1. 100% CPU usage on both processors by msftesql.exe (happened about 4
times).

2. Websites timing out while trying to run queries on FT enabled
databases (usually due to the above).

3. Frequent errors when running queries on FT enabled websites which
result in the errors like "The full text catalog is in an unusable
state" or "the full text catalog is corrupt".

4. FT searches suddenly returning a load of unrelated results which do
not match the search criteria. Eg searching for company names containing
"studio" will bring back several companies where that word does NOT
occur in the indexed columns. However the NUMBER of results returned is
usually correct! The results are random looking, but are always the same
for any particular word until the full text catalog has been rebuilt.
It's almost like the row pointers in the FT index have become out of
sync with the actual data.

4. Today we had an issue whereby NO websites on the server could connect
to their databases, even if those databases did NOT use FT! Restarting
SQL Server the first time did not help. It was eventually fixed by
killing all msftesql.exe processes and THEN restarting SQL Server. Even
this did not totally work so a server restart was necessary.

We're using a newish installation of SQL 2005 Standard Edition with SP2.

FT is hardly being used. Just a couple of columns on two tables. The
tables are small and only have a few thousand rows. A full catalog
rebuild only takes about 10 seconds.

The only odd thing we were doing is replacing all of the data in the
database which uses FT with data from a remote server using SSIS. This
is a job which runs daily at 6am and the problems always seem to start
shortly after this time (sometimes DURING the SSIS packages execution).
I have (today) disabled automatic change tracking and instead scheduled
a FT population instead in case this is causing problems.

What I would like to know is, does anybody know of any common causes of
problems like this which may be affecting us? We are struggling to solve
this problem now and don't really know what to try next.

Secondly, is there a way to ensure that SP2 has been applied to the
msftesql.exe process? (eg, perhaps Full text was installed after the
service pack 2 was applied). Does anybody know the version number of
this file if SP2 has definitely been applied to it?

Thanks,

Nick Gilbert