No search results for 1 only table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Joe - 16 Oct 2006 18:28 GMT

I have spent hours on this problem and I can not even find where to
start. I have full-text enabled on 15 our of about 100 tables in my
database. This copy of the database has been running for about 4
months. Everything was working fine except that search has stopped
working on one table (that I have found) some time in the last week. A
little background:
- SQL Server 2000 Enterprise with SP3 (8.00.818).
- OS is Server 2000 SP4 (5.00.2195).
- It's a 2 node cluster with one node active.
- The database was copied from another server about 4 months ago. The
catalog was rebuilt at that time.
- SQL and agent are running under a domain account that is in the local
administrators group.
- Microsoft Search Service is running as a local system account.
- The gatherlogs files are pretty big, but I don't know what they mean.
The latest one is 40 MB of this:

18513cf7    1c6f145    MSSQL75://SQLServer/2f10007b/0000CCCC    8000000c    0    8007007f
33f39dcd    1c6f146    MSSQL75://SQLServer/2f10007b/0000CCCD    8000000c    0    8007007f
99eef329    1c6f147    MSSQL75://SQLServer/2f10007b/0000CCCE    8000000c    0    8007007f

I have rebuilt the full-text index on the table and I have run
repopulating without errors. Any suggestions on what to look for? How
do I tell if the index for a specific table is populated? I have
checked several other tables and they seem fine.

Thanks in advance.

Reply to this Message

Hilary Cotter - 16 Oct 2006 20:14 GMT

Could you post the entire log here or send it to me offline?

can you also do the following and post it here?
declare @id int

set @id=object_id('tblblogpoststext')

select OBJECTPROPERTYEX(@id,'TableFullTextPopulateStatus')

select OBJECTPROPERTYEX(@id,'TableFulltextDocsProcessed')

select OBJECTPROPERTYEX(@id,'TableFulltextFailCount')

select OBJECTPROPERTYEX(@id,'TableFulltextItemCount')

select OBJECTPROPERTYEX(@id,'TableFulltextPendingChanges')

select OBJECTPROPERTYEX(@id,'TableFulltextPendingChanges')

Signature

Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

>I have spent hours on this problem and I can not even find where to
> start. I have full-text enabled on 15 our of about 100 tables in my
[quoted text clipped - 23 lines]
>
> Thanks in advance.

Reply to this Message

Joe - 17 Oct 2006 14:19 GMT

Hilary, thanks for replying. Eventually all the tables failed and we
got several errors. We opened a support case with Microsoft.

Unfortunately one of the errors we were getting was for a corrupt file,
so we deleted the logs. The Microsoft really really wanted the logs
too so we are in the process of trying to get a file restore. I'll
post the final solution here when I get it as well as a list of what
happened.

Thanks everyone

Reply to this Message

Joe - 20 Oct 2006 14:35 GMT

First let me tell you that this took 2 days and about 10 hours of
Microsoft Support time to fix this issue. The support guys best guess
is that the registry got corrupted. What happened is that the
full-text search stopped working on the main table, but was working on
some of the other tables for a while. Eventually after several hours
all full-text stopped working. We did see some errors similar to those
in KB http://support.microsoft.com/kb/817301. After doing what is said
in the KB, we tried to restart the MS Search service in the cluster
administrator, but it would not start. Basically the MS Search service
would start on the cluster nodes, but not on the cluster. This is were
we needed many hours of Microsoft support. They basically followed KB
http://support.microsoft.com/kb/827449, but also did a lot of other
things like delete registry keys and rebuild cluster resources.

Reply to this Message