Full text index requires dropping and recreating - why?

Question

So I've got a web app running on .net 3.5 connected to a SQL 2005 box.

We do scheduled releases every 2 weeks.

About 14 tables out of 250 are full text indexed.

After not every release, but a few too many, the indexes crap out. They seem to have data in there, but when we try to search them from the front end or SQL enterprise we get timeouts/hangs.

We have a script that disables the indexes, drops them, deletes the catalog and then re creates the indexes. This fixes the problem 99 times out of 100. and the one other time, we run the script again and it all works

We have tried just rebuilding the fulltext index but that doesn't fix the issue.

My question is why do we have to do this ? what can we do to sort the index out?

Here is a bit of the script,

IF  EXISTS (SELECT * FROM sys.fulltext_indexes fti WHERE fti.object_id = OBJECT_ID(N'[dbo].[Address]'))
ALTER FULLTEXT INDEX ON [dbo].[Address] DISABLE
GO
IF  EXISTS (SELECT * FROM sys.fulltext_indexes fti WHERE fti.object_id = OBJECT_ID(N'[dbo].[Address]'))
DROP FULLTEXT INDEX ON [dbo].[Address]

GO



IF  EXISTS (SELECT * FROM sysfulltextcatalogs ftc WHERE ftc.name = N'DbName.FullTextCatalog')
DROP FULLTEXT CATALOG [DbName.FullTextCatalog]
GO




        -- may need this line if we get an error
        BACKUP LOG SMS2 WITH TRUNCATE_ONLY

        CREATE FULLTEXT CATALOG [DbName.FullTextCatalog] ON FILEGROUP [FullTextCatalogs]
        IN PATH N'F:\Data'
        AS DEFAULT
        AUTHORIZATION [dbo]



CREATE FULLTEXT INDEX ON [Address](CommonPlace  LANGUAGE 'ENGLISH')
     KEY INDEX PK_Address
          ON [DbName.FullTextCatalog]
     WITH 
          CHANGE_TRACKING AUTO 
go

Unequaled

Are the 14 tables being dropped and reloaded? – Weevil

No, they sometimes have some data changed but no more than that. Stored procs, triggers and function get dropped and recreated. with SQL multi script as part of the release – Unequaled

It seems as though you may have some kind of problem – Sexology

Thanks for that mate, I wish I had thought of that earlier. You've been alot of help – Unequaled

no problem, it is always a pleasure to help out a fellow developer – Sexology

Do you love WoW by any chance? Paladins do it for you?? – Unequaled

I do enjoy Paladins, but Druids more so. – Sexology

Stop the kissing and post one answer so Amjid can check it as answered :) – Lorca

I believe the answer is 5768374 – Sexology

Man, who shut this down? What a buzz kill. – Sexology

Answer 1

This MSDN article on Full Text Indexing Failures in SQL Server 2005 lists 6 possible causes. I'm summarizing here - see the full article for details.

• The indexer cannot find or load a filter or word breaker component.
• A component, such as a word breaker or filter, fails and returns an error to the indexer.
• The full-text index exceeds the limit for the number of rows that can be contained in a full-text catalog.
• A clustered index or full-text key index on the table being indexed gets altered, dropped, or rebuilt.
• A hardware failure or disk corruption results in the corruption of the full-text catalog.
• A file group that contains the table being full-text indexed goes offline, or is made read-only.

The conclusion of the article is:

You should view the crawl log at the end of any significant full-text index population operation, or when you find that a population did not complete.

Hittite