How long does it take to drop a FT index?

Yesterday I created a full text index on a single field in my database with
365000 rows in order to do some performance testing to see if moving to a
full text index would provide significant improvement over LIKE clauses for
looking up words in titles of products. I now need to create indexes on a
couple more rows, and so I need to drop the existing index and create a new
one. However, it's now 3 hours later and iSQL is still running my drop
statement. How long does it normally take to drop a full text index of 98045
unique words with 364704 items, totalling 13MB? If I right click on the
catalog in SQL Ent Manager and choose Properties it shows the catalog as
idle. No errors are being returned by iSQL, and there is nothing in either
the W2K Event Log or the SQL log to indicate there is a problem. Please help
:|
Dan
This should happen well within a minute. run sp_lock to see if there is any
locking.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:emlT0SGrEHA.868@.TK2MSFTNGP10.phx.gbl...
> Yesterday I created a full text index on a single field in my database
with
> 365000 rows in order to do some performance testing to see if moving to a
> full text index would provide significant improvement over LIKE clauses
for
> looking up words in titles of products. I now need to create indexes on a
> couple more rows, and so I need to drop the existing index and create a
new
> one. However, it's now 3 hours later and iSQL is still running my drop
> statement. How long does it normally take to drop a full text index of
98045
> unique words with 364704 items, totalling 13MB? If I right click on the
> catalog in SQL Ent Manager and choose Properties it shows the catalog as
> idle. No errors are being returned by iSQL, and there is nothing in either
> the W2K Event Log or the SQL log to indicate there is a problem. Please
help
> :|
> Dan
>
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eX8NLVGrEHA.896@.TK2MSFTNGP12.phx.gbl...
> This should happen well within a minute. run sp_lock to see if there is
any
> locking.
I knew I'd forgotten to do something :|
Thanks for the help. I found a stuck connection from a process that ran a
9:26 last night that for reason hadn't cleared - the process ended after
only a few seconds, yet SQL Server was maintaining the lock. In the end I
had to restart the SQL Server service to get rid of it, then the index
dropped in 5 seconds.
Dan
|||You have to be very careful with locking. SQL FTS does apply row level
locking while doing populations which can aggravate deadlocks and
maintenance operations like the one you have discovered.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:eSpySqGrEHA.452@.TK2MSFTNGP09.phx.gbl...
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eX8NLVGrEHA.896@.TK2MSFTNGP12.phx.gbl...
> any
> I knew I'd forgotten to do something :|
> Thanks for the help. I found a stuck connection from a process that ran a
> 9:26 last night that for reason hadn't cleared - the process ended after
> only a few seconds, yet SQL Server was maintaining the lock. In the end I
> had to restart the SQL Server service to get rid of it, then the index
> dropped in 5 seconds.
> Dan
>