November 28, 2008 at 8:40 am
I have a table of several million records with a description column of 4000 characters. I need to write something that searches for 1 of approximately 6000 strings within each of the records.
FULL-TEXT seemed to be the way to go so I googled implementation of it and created a full text index for the description column of the table. As soon as I ran it I got the message "Full-text catalog is in an unusable state. Drop and re-create this full-text catalog."
I've done this repeatedly but still get the same error. I've also googled the problem and everyone else seems to drop and re-create it successfully.
The message is pretty unhelpful and I can't find out what the underlying cause of the error is.
Any ideas, folks?
November 28, 2008 at 9:01 am
The problem just got worse.
I wasn't running anything but I got a call from Tech Support because the server had gone into overdrive. As soon as I dropped the index it went calm again.
Looks like I will have to use LIKE to find my data after all
November 28, 2008 at 9:07 am
Sounds like a strange error, I have not encountered this before, perhaps it is a problem with the server or disks. It may be worthwile to get the IT guys to check it out..
November 28, 2008 at 10:00 am
When you create your FTC, what are you setting the populate frequency to?
November 28, 2008 at 11:12 am
Creating a Full-Text index can take a long time (minutes, hours, days, I've even seen weeks),
Which might explain the "Unusable state" messages (because it isn't done yet).
And it can really hammer your system while it is doing it. Which definitely explains your calls from tech-support.
SQL Server 2005 is supposed to be better about this than 2000, and there are supposed to be ways to control this, but I do not know enough about full-text indexing to say what.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply