Full text index requires dropping and recreating - why?

Question

Hi all,

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

Answer 1

The answer is 5768374