Full Text Search - How do I best update Index - Merge Replication

Hi,
How do I best update a Full Text Index on SQL2000 sp3, for Merge Replication
(Push Subscription)?
Here is the config:
Site 1:
I have one table with one column (ntext), which I have created a full text
index.
The table updated once per day, data is never updated, always insert new
rows.
The table is Read-Only.
Everyone in Site 1 is happy... full text working good.
Site2: - Branch Office
I have created a merge replication, (push subscription on Site1):
We now have a copy of this read-only database/table in site #2.
I created a full text index on it, this is also working good.
Now after 24 hours, Site 1 gets 1000 new rows of data.
Since I have a push subscription, these new 1000 rows of data will get
pushed to site 2.
How do I automatic the full text index so it will add these new rows (column
data) to the full text index?
Once again, these are not updates, always new rows of data.
I would like to avoid using the Full Text Scheduler.
If I configured a Trigger on Site 2, how would it know when the 1000 rows
where finished inserting rows?
Thanks
Russell Mangel
Las Vegas, NV
use change tracking with update index in background on the subscriber.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Russell Mangel" <russell@.tymer.net> wrote in message
news:eDrSSZHeEHA.2384@.TK2MSFTNGP09.phx.gbl...
> Hi,
> How do I best update a Full Text Index on SQL2000 sp3, for Merge
Replication
> (Push Subscription)?
> Here is the config:
> Site 1:
> I have one table with one column (ntext), which I have created a full text
> index.
> The table updated once per day, data is never updated, always insert new
> rows.
> The table is Read-Only.
> Everyone in Site 1 is happy... full text working good.
> Site2: - Branch Office
> I have created a merge replication, (push subscription on Site1):
> We now have a copy of this read-only database/table in site #2.
> I created a full text index on it, this is also working good.
> Now after 24 hours, Site 1 gets 1000 new rows of data.
> Since I have a push subscription, these new 1000 rows of data will get
> pushed to site 2.
> How do I automatic the full text index so it will add these new rows
(column
> data) to the full text index?
> Once again, these are not updates, always new rows of data.
> I would like to avoid using the Full Text Scheduler.
> If I configured a Trigger on Site 2, how would it know when the 1000 rows
> where finished inserting rows?
> Thanks
> Russell Mangel
> Las Vegas, NV
>
>
>
|||Thanks for your reply, I will try this.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:ub1IZWPeEHA.2908@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> use change tracking with update index in background on the subscriber.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Russell Mangel" <russell@.tymer.net> wrote in message
> news:eDrSSZHeEHA.2384@.TK2MSFTNGP09.phx.gbl...
> Replication
text[vbcol=seagreen]
> (column
rows
>

Full text issue

Hello,

I run a music related website (www.LowestCostMusic.com) and I'm using the SQL2000 full text search engine.

If you go to the site via this link... It would be searching on the phrase 'the way it is'

http://www.lowestcostmusic.com/search2.asp?search=the+way+it+is

Which is all noise words... yet could be a title of some sheet music that we carry. I need to know how to build the SQL full text catalog to allow this search to work properly... Any ideas? Thanks!

-MattMatt,

Thanks for using Microsoft SQL Server 2000 and Full-Text Search on your web site. This should help:

You need to update your noise words list and then repopulate your Full-Text index in order to accept searches on titles such as "the way it is" . You may find your noise word files under:

\Program Files\Microsoft SQL Server\MSSQL$INSTANCENAME\Ftdata\SQLServer\Config\ noise.enu (for US English)

You may add words to be excluded from the index, and likewise remove words that you would like included.

Once you are satisfied with changes, restart MSSearch service and you will need to repopulate your catalogs.

This should fix your problem right away.

All the best,
--andrew

Full population very slow

I've got SQL2000 Enterprise edition running on windows 2000 advanced server,
we've recently upgraded from SQL sp2 to sp3a, this caused a catalog rebuild
but full population is taking forever. The column with Full text search
enabled is of image type, there are 2.5 million record in the table, about
7GB in size.
I've started full population 2 days ago and it's still showing "Population
in progress", when I view the property page of the catalog, I can see ~
2300000 items, catalog size: 190MB, unique key count: 362586. It appears to
be processing 1000 items per minute.
The server has 8 CPUs, 8GB RAM with /PAE and /3GB enabled, heavy duty SAN
disks.
mssdmn.exe is the most active process, it's using between 30-40 CPU on one
processor only, disk utilization is minimal and plenty of physical memory
available for use. This process is using 6 threads, 10MB RAM, mssearch.exe is
using 50MB RAM and 16 threads.
Is there any way of speeding up population?
Thanks,
Ad
set sp_fulltext_service 'resource_usage', 5
"Ad" <Ad@.discussions.microsoft.com> wrote in message
news:B94E0F3A-7655-4D8A-8605-EA1BC43E6329@.microsoft.com...
> I've got SQL2000 Enterprise edition running on windows 2000 advanced
> server,
> we've recently upgraded from SQL sp2 to sp3a, this caused a catalog
> rebuild
> but full population is taking forever. The column with Full text search
> enabled is of image type, there are 2.5 million record in the table, about
> 7GB in size.
> I've started full population 2 days ago and it's still showing "Population
> in progress", when I view the property page of the catalog, I can see ~
> 2300000 items, catalog size: 190MB, unique key count: 362586. It appears
> to
> be processing 1000 items per minute.
> The server has 8 CPUs, 8GB RAM with /PAE and /3GB enabled, heavy duty SAN
> disks.
> mssdmn.exe is the most active process, it's using between 30-40 CPU on one
> processor only, disk utilization is minimal and plenty of physical memory
> available for use. This process is using 6 threads, 10MB RAM, mssearch.exe
> is
> using 50MB RAM and 16 threads.
> Is there any way of speeding up population?
> Thanks,
> Ad
>
|||Ad,
There are a couple of thing you can do to improve the FT Indexing
performance without stopping & re-locating the FT Catalog. You can set the
MSSearch service's to use one CPU and then use sp_configure to set the
affinity of SQL Server to that cpu to avoid cpu contention during heavy
MSSearch CPU usage via the following method - launch the Tskmgr.exe from the
AT command on the multi-proc server where sql server resides, as follows:
at <current_time+1min> /interactive taskmgr.exe
when it launches, you can then set "cpu affinity" for the MSSearch service
to a cpu or set of cpu's not being used by SQL Server. Then you would use
sp_configure to set SQL Server's cpu affinity to the other cpu's, thus
preventing the cpu usage of mssearch from affecting your sql server
processing. Note, this only works on multi-processor servers and is a
secured method for achieving this feature.
Additionally, as your server has lots of RAM, you can use
sp_fulltext_service 'resource_usage', 5 to set the MSSearch service to 5
(dedicated), and it will use the max amount of RAM (512Mb), but only if the
RAM is not being used by either the OS or by SQL Server.
Finally, I'd highly recommend that you review all the resources "SQL Server
2000 Full-Text Search Resources and Links" at:
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Ad" <Ad@.discussions.microsoft.com> wrote in message
news:B94E0F3A-7655-4D8A-8605-EA1BC43E6329@.microsoft.com...
> I've got SQL2000 Enterprise edition running on windows 2000 advanced
server,
> we've recently upgraded from SQL sp2 to sp3a, this caused a catalog
rebuild
> but full population is taking forever. The column with Full text search
> enabled is of image type, there are 2.5 million record in the table, about
> 7GB in size.
> I've started full population 2 days ago and it's still showing
"Population
> in progress", when I view the property page of the catalog, I can see ~
> 2300000 items, catalog size: 190MB, unique key count: 362586. It appears
to
> be processing 1000 items per minute.
> The server has 8 CPUs, 8GB RAM with /PAE and /3GB enabled, heavy duty SAN
> disks.
> mssdmn.exe is the most active process, it's using between 30-40 CPU on one
> processor only, disk utilization is minimal and plenty of physical memory
> available for use. This process is using 6 threads, 10MB RAM, mssearch.exe
is
> using 50MB RAM and 16 threads.
> Is there any way of speeding up population?
> Thanks,
> Ad
>
|||sp_fulltext_service 'resource_usage', 5 , did not make any difference, does
it need stop and re-start of MSSearch service?
Thanks,
Ad
|||Ad,
I didn't think it would make much difference. However, where you get the
biggest bang for your buck, is ensuring that the FT Catalog is on a separate
disk drive from your database files. Can you stop the Full Population, drop
and re-create the FT Catalog on separate disk array or better yet, on a
separate disk controller? See SQL 2000 BOL title "Full-text Search
Recommendations" for more info on this.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Ad" <Ad@.discussions.microsoft.com> wrote in message
news:B72E38AF-4FFF-4F60-9F84-BF56213AC220@.microsoft.com...
> sp_fulltext_service 'resource_usage', 5 , did not make any difference,
does
> it need stop and re-start of MSSearch service?
> Thanks,
> Ad
>
>
|||The FT Catalog is on a dedicated SAN disk which is almost idle, in fact the
whole disk is cached. So there are plenty of resources on the server that FT
can't utilize, this leads me to believe FT has scalability issues.
Regards,
Ad
"John Kane" wrote:

> Ad,
> I didn't think it would make much difference. However, where you get the
> biggest bang for your buck, is ensuring that the FT Catalog is on a separate
> disk drive from your database files. Can you stop the Full Population, drop
> and re-create the FT Catalog on separate disk array or better yet, on a
> separate disk controller? See SQL 2000 BOL title "Full-text Search
> Recommendations" for more info on this.
> Regards,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Ad" <Ad@.discussions.microsoft.com> wrote in message
> news:B72E38AF-4FFF-4F60-9F84-BF56213AC220@.microsoft.com...
> does
>
>

Full mode to Simple mode in SQL2000?

Hi all,
I have two DBs that set as Full mode which has a t-log and
it seems like contains some data.
These DBs are updated by clients 24/7
If I set it to Simple mode, does data from Log will be
updated to DB and truncate it automatically? And not
affect any transaction from clients?
Also, how can I see if there is any data in Transaction
log? Any Stored procedure in query analyzer?
Thanks in advance.
Lintadsl"Lintadsl" <anonymous@.discussions.microsoft.com> wrote in message
news:000f01c3a551$cd775630$a401280a@.phx.gbl...
> Hi all,
> I have two DBs that set as Full mode which has a t-log and
> it seems like contains some data.
> These DBs are updated by clients 24/7
> If I set it to Simple mode, does data from Log will be
> updated to DB and truncate it automatically?
Yes it will.
>And not affect any transaction from clients?
It will not effect any active transaction.
> Also, how can I see if there is any data in Transaction
> log? Any Stored procedure in query analyzer?
You would need a 3rd party product like lumnigent to view any data in the
transaction log. You can use DBCC OPENTRAN to see if there are any open
transactions.
You also need to be aware that if you switch the database from Full to
Simple recovery you loose the ability to recover any transactions, since the
last full database backup.
Steve|||>--Original Message--
>"Lintadsl" <anonymous@.discussions.microsoft.com> wrote in
message
>news:000f01c3a551$cd775630$a401280a@.phx.gbl...
>> Hi all,
>> I have two DBs that set as Full mode which has a t-log
and
>> it seems like contains some data.
>> These DBs are updated by clients 24/7
>> If I set it to Simple mode, does data from Log will be
>> updated to DB and truncate it automatically?
>Yes it will.
>>And not affect any transaction from clients?
>It will not effect any active transaction.
>> Also, how can I see if there is any data in Transaction
>> log? Any Stored procedure in query analyzer?
>You would need a 3rd party product like lumnigent to view
any data in the
>transaction log. You can use DBCC OPENTRAN to see if
there are any open
>transactions.
>You also need to be aware that if you switch the database
from Full to
>Simple recovery you loose the ability to recover any
transactions, since the
>last full database backup.
>Steve
>
>.
>