sql server full text search: I have a database where the full-text catalogs can be manually populated
(full or incremental) and searching against the catalogs are fine.

However, whenever a table is updated, the changes are not being applied to
the appropriate catalog.

We have bounced MSSearch, SQL Server agent, SQL Server, and finally
re-booted the system. None of this worked.

The catalogs have been working for several months, but suddenly the udpates
are failing.

SQL Server 2000 Enterprise, clustered. SQL and it's databases are install

Ed,
Could you post the full output of -- SELECT @@version -- as this would be
most helpful in troubleshooting this FTS issue.
Could you also review and post any errors from your server's Application
event log source events: "Microsoft Search" or MssCi as this will provide
more information on what caused the updates from failing. Since this is a
clustered environment, have you recently failed over?

Thanks,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/


[quoted text, click to view]

I've run into this. I'm not sure why it happens but it seems that when you
failover a node the catalogs get read only. Here are the instructions I put
together on it. The first part is a script that runs every 5 minutes to
check to see if the catalogs are read only. It does this by creating a
table, fulltext indexing it with update index in background. Then it pushes
a row in, checks a minute later to ensure it is indexed, if not an alert is
raised.

The next part deals with fixing the problem.

Please check it out at http://www.indexserverfaq.com/clusterfailure.htm

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

[quoted text, click to view]

Hello, I am also seeing this problem, and tried those solutions.

"Update in Background" is enabled, and records are getting inserted
into sysfulltextnotify. I can use sp_fulltext_table <Table_Name>,
'Update_index' to populate the index. I can create test tables in the
same db and updates WILL happen automatically. There are no error or
info messages in the windows app logs, and there aren't any error logs
for MSSearch (anyone know how I can set that?)

Thanks,
Steve

Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003
16:08:15 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
Edition on Windows NT 5.2 (Build 3790: )
also on a cluster.

Steve,
As I'm sure you've discovered, troubleshooting SQL FTS issue can be
difficult, and as I've said in the past, akin to trying to discern what is
behind a blackhole ;-)

When this problem does occur, your first point of review is the server's
Application Event log as this is one source of information for the
"Microsoft Search" or MssCi source events. However, these events can roll
off your server event log, so you may want to reset the app log to retain
data longer. Additionally, there are binary Gather log files under the
folder \FTDATA\SQLServer\GatherLogs\ where you have SQL Server installed.
You can use the gthrlog.vbs app to read these files. Below is an Exchange KB
article that documents the use of the vbs file as this can be used for SQL
Server / MSSearch as well as exchange, just change the path info:

XADM: Using the Gthrlog.vbs Utility to View Gather Logs
http://support.microsoft.com/?kbid=270058

C:\Program Files\Common Files\Microsoft Shared\MSSearch\Bin
or
c:\Program Files\Common Files\System\MSSearch\Bin

-- another example of use:
cd\
cscript //nologo gthrlog.vbs
drive_letter:<full_Path>\FTDATA\SQLServer\GatherLogs\ *.gthr file

Hopefully, with the above info, and if a gthr file has the errors that
occurred, you can post them here for further analysis!
Thanks,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/



[quoted text, click to view]

All - Thanks for the suggestions for far. The Gthrlog.vbs utility
didn't reveal any errors.

-Steve

Thanks for the tips...I'm looking into the suggestions now.

The @@version info is:
Microsoft SQL Server 2000 - 8.00.955 (Intel X86) Jun 23 2004 12:39:39
Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)


Our DBA team (of which I'm not a member of) has engaged Microsoft Tech
Support. They are looking into the problem as a type (literally, I'm on a
conference call with them).

Whatever the results, I will be sure to share them with everyone.

Thanks,

You're welcome, Ed,
You're using SQL Server 2000 (build 955 - SP4 beta?) on Win2K SP4.

Yes, please do share what you find out from MS Tech Support on this issue
"Update Index in Background" not updating

Thanks,
John

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


[quoted text, click to view]

Solution!

Ok, after 2 hours on the phone with Microsoft, we all determined that there
was no documentation on this type of error...imagine that.

It appears that the update method was behaving not in "Background" as the
settings said, but was actually "on demand". There were no errors in any
logs (application, system, sql, gather).

To correct the problem, we had to execute the "sp_fulltext_table" procedure
to:
stop_change_tracking
start_change_tracking
stop_background_updateindex
start_background_updateindex

Performing the same actions in Enterprise Manager was not producing results.
It worked only through sql. Also, it took stopping and starting both items.
Just doing one or the other wouldn't work.

Yup, it had to be ran on each individual table. The following query will
give you the list of full-text catalog tables in your database:
select [name] from sysobjects where ftcatid <> 0 and type = 'U'

May this help others. And thanks to all that looked at it.

You're welcome, Ed,
Excellent news & hopefully MS Tech support filed a bug for you on this issue
as I believe it to be a bug.

Thank you for posting this follow-up info!
John

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


[quoted text, click to view]

Ed, your solution also fixed my problem. A big THANKS!