Very Computer

by **Dan Meie** » Fri, 14 Sep 2001 04:18:09

We have a database that has change-tracking turned on. We
have many inserts and deletes occuring on the indexed
table. I find that if I perform many inserts/deletes very
quickly that the indexing seems to 'loose' records. The
table record count increases, but the number of items
(Item Count) in the full-text index remains about the
same. If I slow down the process to about 1 record every
10 seconds, then I seems to work properly. Any ideas?

by **Trevor Dwye** » Fri, 14 Sep 2001 06:20:13

Have you checked the index the following day - after the master merge has
taken place? Are you able to query the results with the correct data or is
it simply the metadata that is incorrect?

Best Regards

Trevor Dwyer - SQL Server MVP

Quote:> We have a database that has change-tracking turned on. We
> have many inserts and deletes occuring on the indexed
> table. I find that if I perform many inserts/deletes very
> quickly that the indexing seems to 'loose' records. The
> table record count increases, but the number of items
> (Item Count) in the full-text index remains about the
> same. If I slow down the process to about 1 record every
> 10 seconds, then I seems to work properly. Any ideas?

by **John Kan** » Fri, 14 Sep 2001 13:13:42

Dan,
You should review the NT/Win2K Application event log on the server where you
have SQL Server 2000 installed for source events "Micrsoft Search" and MssCi
to see if any related MSSearch errors are recorded durung the times that
that change tracking seems to "loose" records. Also, do these "lost" rows,
seem to return later, say after midnight? Also, if you run an Incremental or
Full Population, do you regain these "lost" rows?

Furthermore, in your situation with mass/fast update, Change Tracking with
Update Index in Background, might not be the best stragety for ensuring
accurate FTS queries. Checkout SQL 2000 BOL title "Maintaining Full-Text
Indexes":
"The method you use depends on factors such as the CPU and available memory,
the amount and rate of change of data, the amount of available disk space,
and the importance of the full-text index being current. Use these
recommendations as a guide for selecting a maintenance method.
a.. Use change tracking with the background update index option when CPU
and memory are available, the value of an up-to-date index is high, and
immediate propagation can keep up with the rate of changes.

b.. Use change tracking with scheduled propagation when CPU and memory can
be used at scheduled times, disk space for storing changes is available, and
changes between the scheduled times are not so significant that the
propagation takes longer than a full rebuild.

c.. Use a full rebuild when a large percentage of records change or are
added at once. If a large percentage of records change over an extended
period of time, consider using change tracking with scheduled or background
update index.

d.. Use an incremental rebuild when a large number, but not a large
percentage, of documents change at one time. If a large number of records
change over an extended period of time, consider using change tracking with
scheduled or background update index. "

Quote:> We have a database that has change-tracking turned on. We
> have many inserts and deletes occuring on the indexed
> table. I find that if I perform many inserts/deletes very
> quickly that the indexing seems to 'loose' records. The
> table record count increases, but the number of items
> (Item Count) in the full-text index remains about the
> same. If I slow down the process to about 1 record every
> 10 seconds, then I seems to work properly. Any ideas?

Very Computer

Change tracking looses changes

Change tracking looses changes

Change tracking looses changes

Change tracking looses changes