SQL 7 FTS: any way to retrieve the indexed terms for a row?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Daniel Crichton - 21 Mar 2005 11:05 GMT

I have FTS enabled on a number of columns on a SQL 7 database table, and
today for the first time I've had a strange result for a search term. The
term does not exist, and has never existed, in the column I'm querying, and
yet using CONTAINS(QuickSearch,'"0130606219"') as the only WHERE clause is
returning 2 rows from my table that do not include this string. I have tried
shortening the term, and searching for "013" also returns the same 2 rows
(trying "01" brings back a whole load more rows that have 01 as a term in
the column which is what I expect to see, and does not include the first row
that has the column data below, which is correct) - yet neither contain this
string either. The contents of that column for these 2 rows are:

FOUNDATIONS OF REAL TIME COMPUTING SCHEDULING AND RESOURCE MANAGEMENT THE
KLUWER INTERNATIONAL SERIES IN ENGINEERING COMPUTER SCIENCE 0141 RE DATA
IN COMPUTER SYSTEMS KLUWER ACADEMIC PUBLISHERS

MALAWI 2000 ARTICLE IV CONSULTATION AND REQUEST FOR A THREE YEAR ARRANGEMENT
UNDER THE POVERTY REDUCTION AND GROWTH FACILITY IMF COUNTRY REPORT NO 01 38
DEVELOPMENT ECONOMICS THE STATIONERY OFFICE BOOKS

As you can see, neither contain the string being searched for.

Is there any way to request the indexed terms for a row from the FTS catalog
so that I can see what has been indexed for these 2 rows? Or am I just going
to have to rebuild the entire catalog and hope that the problem gets solved?
A rebuild would be something I'd prefer not to do - this a 390,000 row table
that originally took around 10 hours to build the catalog for, and being a
production web server I can't really afford for it to be down for 10 hours
again (although if I have no choice I can switch my queries to use LIKE
matches on the column rather than the FTS catalog easily enough, although it
does tend to be slower).

Dan

Reply to this Message

John Kane - 21 Mar 2005 17:30 GMT

Daniel,
Could you post the full output of -- SELECT @@version -- as this would be
most helpful information in understanding your environment.

Can you confirm the contents of the UK English noise word file noise.eng
under \FTDATA does not contain any single digits?

Thanks,
John

Signature

SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/

> I have FTS enabled on a number of columns on a SQL 7 database table, and
> today for the first time I've had a strange result for a search term. The
[quoted text clipped - 28 lines]
>
> Dan

Reply to this Message

Daniel Crichton - 21 Mar 2005 17:39 GMT

John wrote on Mon, 21 Mar 2005 08:30:21 -0800:

> Daniel,
> Could you post the full output of -- SELECT @@version -- as this would be
> most helpful information in understanding your environment.

Microsoft SQL Server 7.00 - 7.00.961 (Intel X86)
Oct 24 2000 18:39:12
Copyright (c) 1988-1998 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 2)

I know, I should really get it patched up, but I need to get my mirrored
server up first to minimise the web site downtime.

> Can you confirm the contents of the UK English noise word file noise.eng
> under \FTDATA does not contain any single digits?

noise.enu and noise.eng both contain only a single line with a space on it.

Dan

Reply to this Message

Hilary Cotter - 21 Mar 2005 18:03 GMT

I can't seem to repro your problem. Which word breaker are you using?

Could you do a select @@language on your system for me?

Signature

Hilary Cotter
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 FTS enabled on a number of columns on a SQL 7 database table, and
> today for the first time I've had a strange result for a search term. The
[quoted text clipped - 28 lines]
>
> Dan

Reply to this Message

Daniel Crichton - 21 Mar 2005 18:27 GMT

Hilary wrote on Mon, 21 Mar 2005 12:03:13 -0500:

> I can't seem to repro your problem. Which word breaker are you using?

Neutral word breaker.

I can only assume that something has become corrupted in my catalog. That
was why I was hoping that there was a way to retrieve all of the terms
indexed against a particular row for checking. The text in these columns has
not changed since the row was initially created.

I've just been digging through my event log and noticed that something funny
happened this week - it appears the incremental population that started on
Saturday morning and would normally take around 20 mins to run didn't finish
till after midnight last night. I run a full population every Sunday evening
at 20:00 that normally takes around 4 hours to run - so the 40 odd hours for
the incremental is worrying. I'm going to schedule a full pop for tonight
and see if that cures the problem.

> Could you do a select @@language on your system for me?

us_english

Dan

Reply to this Message

Hilary Cotter - 21 Mar 2005 18:50 GMT

An incremental population can take longer to run than a full population if a
large portion of your table has been deleted. this might explain your
problem. Did you check the gatherer logs for any messages?

Signature

Hilary Cotter
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

> Hilary wrote on Mon, 21 Mar 2005 12:03:13 -0500:
>
[quoted text clipped - 20 lines]
>
> Dan

Reply to this Message

Daniel Crichton - 21 Mar 2005 19:08 GMT

Hilary wrote on Mon, 21 Mar 2005 12:51:27 -0500:

> An incremental population can take longer to run than a full population if
> a large portion of your table has been deleted. this might explain your
> problem. Did you check the gatherer logs for any messages?

The data doesn't change that much in this database. Deletions tend to be no
more than around 500 rows a day, and insertions maybe 500 a day. It's a
sanitised version of our product database with almost 400,000 rows in total,
and being 99% books the FTS indexed columns don't change much (title,
author, publisher and subject stay pretty constant for the life of a
product). The gatherer logs showed nothing unusual that I could see, but it
appears it had been overwritten by the incremental update that ran this
morning. There are loads of MssCi event log entries saying that more than
40000 entries had changed - 19 in fact, which suggests that every single row
was marked as deleted, and every single one marked as inserted. The end of
the incremental crawl showed the following:

Detected end of incremental crawl for project <SQLServer SQL0002300005>.
Successfully processed 392054 documents, 0K. Failed to filter 392555
documents. Modified 392054 documents.

That incremental should have picked up only 501 rows as requiring a catalog
update (which is the difference between the two counts).

Dan

Reply to this Message

Daniel Crichton - 21 Mar 2005 19:47 GMT

Daniel wrote to Hilary Cotter on Mon, 21 Mar 2005 18:08:54 -0000:

> Hilary wrote on Mon, 21 Mar 2005 12:51:27 -0500:
>
[quoted text clipped - 5 lines]
> it appears it had been overwritten by the incremental update that ran this
> morning. There are loads of MssCi event log entries saying that more than

No, my mistake - overwritten as I'd already started the rebuild :\

I'm restoring the gatherer log from my backup tape, I'll trawl through it in
the morning (it's 18:47 here and I should left work over an hour ago). It's
32Mb, much bigger than I remember the gatherer logs being in the past.

Dan

Reply to this Message

Daniel Crichton - 22 Mar 2005 09:44 GMT

Daniel wrote to Daniel Crichton on Mon, 21 Mar 2005 18:47:42 -0000:

> Daniel wrote to Hilary Cotter on Mon, 21 Mar 2005 18:08:54 -0000:
>
[quoted text clipped - 15 lines]
> It's
> 32Mb, much bigger than I remember the gatherer logs being in the past.

The rebuild and full population has fixed the issue I was seeing, so it's
definitely looking like catalog corruption. The new catalog is 39Mb
containing 396492 items and 406417 unique keys, which looks similar to the
values I saw on the corrupted version yesterday, so no clues there.

I've got the gatherer log from the weekend - what exactly am I looking for?

Dan

Reply to this Message

Hilary Cotter - 22 Mar 2005 12:02 GMT

post it here as an attachment or send it to me offline

Signature

Hilary Cotter
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

> Daniel wrote to Daniel Crichton on Mon, 21 Mar 2005 18:47:42 -0000:
>
[quoted text clipped - 26 lines]
>
> Dan

Reply to this Message

Daniel Crichton - 21 Mar 2005 18:53 GMT

I can only assume something is very wrong on my server. If I try to start a
full population, I get back no error from EM but refreshing the FT part of
the server shows the catalog as idle, and nothing is written to the event
log. I've now tried a rebuild catalog which successfully deleted and
recreated it, and kicked off a full population that is now showing as in
progress. I have flagged my search scripts to not use FTS clauses - I'm glad
I put that into my COM object as a property so I could turn it on and off
for testing ;) I'll check the results for the strings I've been having
problems with again in the morning and post a followup.

Dan

Reply to this Message

Daniel Crichton - 22 Mar 2005 09:58 GMT

Daniel wrote to Hilary Cotter on Mon, 21 Mar 2005 17:28:50 -0000:

> Hilary wrote on Mon, 21 Mar 2005 12:03:13 -0500:
>
>> I can't seem to repro your problem. Which word breaker are you using?
>
> Neutral word breaker.

Sorry, that was wrong. I misread the line in sp_configure. I'm pretty sure
it's the UK word breaker as I installed SQL using the default collation
setting, and Windows 2000 was configured as UK.

Dan

Reply to this Message