Re: Locks & FT Catalogs

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

No it certainly doesn't sound like normal blocking. Perhaps sql fts put a
schema level lock in place while creating the catalog and this got
deadlocked causing the problem you see.

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



"Martin Smith" <MartinSmith@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6FBCB8C5-4E57-4955-B196-4F15E35CB245@xxxxxxxxxxxxxxxx
Hi Hilary,


Apologies for the triple post it didn't look like it had been successfully
submitted!

This didn't seem like normal locking due to large tables.

Everything ground to a halt.

The spid of the query inserting only 14 rows into the full text indexed
table was suspended for 2 hours (after which I killed it) waiting for the
system FT CATLG MONITOR system spid to release it's lock (which it didn't
do
in the 6 hours after that either) leaving that catalog completely unusable
until SQL was restarted.

I'm pretty sure it's either a bug in the product or a configuration error
on
our part.

As far as you know should we be able to just copy the mdf files without
causing any problems to SQL 2005 RE: Full Text Indexing or are we meant to
rename one of the catalogues?

The approach we were using didn't seem to cause any issues when we were on
SQL 2000.

Cheers,

Martin

"Hilary Cotter" wrote:

For large tables you will get better indexing and querying performance
(and
less locking) if you partition your table into two or more tables. A
table
can only belong to one catalog however (unless you full-text index views
which won't help your locking problem).

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



"Martin Smith" <MartinSmith@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:09846483-0945-4754-BB78-31ED417815B4@xxxxxxxxxxxxxxxx
We have a site with 2 copies of the database a live and a staging one.
Our
client have an Admin facility to update the staging database and can
then
synchronise their changes from staging to live (or vice versa to roll
back).


Historically where we have needed to do large changes to the database
we
have made sure they have no outstanding changes to synchronize, made
the
changes to the staging version copied the mdf file, then detached the
live
database and reattached the copied mdf file with the correct name for
the
live database.


Today I encountered a locking problem where an insert on the staging
database into the full text indexed table (with automatic change
tracking
enabled) was timing out indefinitely. I investigated the locking and
found
someone else with the exact same symptons here
http://www.mcse.ms/archive89-2005-11-1976505.html (Basically a system
spid
showing up in activity monitor as FT CATLG MONITOR had a Wait Type
LCK_M_SCH_S on Resource FULLTEXT_CATALOG and wouldn't release it until
SQL
restarted). Trying to expand the "Full Text Catalogs" node in the
staging
copy of the database gave a lock time out error. However I could expand
it
in
the live copy of the database (and do Full Text index queries on the
live
database).


I'm wondering whether after cloning the databases we need to separate
the
Full Text Indexing into 2 different catalogues to avoid this kind of
issue?
Is what we have been doing completely wrong insofar as Full Text
Indexing
is
concerned? Or shouldn't there be a problem with tables in different
databases
using the same Full Text Catalog?

Thanks in advance for your advice.







.

Relevant Pages

  • Re: Benchmark for Full Text Search
    ... We ended up breaking the database into smaller databases of about 50 million rows. ... Searches were directed against each database - each db had a single catalog and a single sql fts index. ...
    (microsoft.public.sqlserver.fulltext)
  • Full-Text options disabled in management console
    ... If I open a SQL 2000 database using the management console the Full-Text ... of SQL 2000 and on server editions installed on servers. ... right click on the database "New Full-Text Catalog is grayed out/disabled. ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Locks & FT Catalogs
    ... Director of Text Mining and Database Strategy ... system FT CATLG MONITOR system spid to release it's lock (which it didn't ... in the 6 hours after that either) leaving that catalog completely unusable ... synchronise their changes from staging to live (or vice versa to roll ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Locks & FT Catalogs
    ... This didn't seem like normal locking due to large tables. ... in the 6 hours after that either) leaving that catalog completely unusable ... Director of Text Mining and Database Strategy ... client have an Admin facility to update the staging database and can then ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Unique key count = 1
    ... Right click on your database, select full-text index and make sure you have ... If it is can you post your gatherer log here. ... Looking for a SQL Server replication book? ... I have created a new fulltext catalog and a done a full population. ...
    (microsoft.public.sqlserver.fulltext)