A full text index needs to be reset and recreated - why?

Question

So, I have a .net 3.5 web application connected to a SQL 2005 mailbox.

We plan to release every two weeks.

About 14 tables out of 250 are indexed with full text.

After not every release, but a little too much, the indexes fight. They seem to have data, but when we try to look for them from an external interface or SQL enterprise, we get timeouts / hangs.

We have a script that disables indexes, omits them, deletes the directory, and then re creates the indexes. This fixes the problem 99 times out of 100. and another time we run the script again and everything works

We tried to simply rebuild the full-text index, but this does not fix the problem.

My question is, why should we do this? what can we do to sort the index?

Here is a little 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

This MSDN article about Full Text indexing errors in SQL Server 2005 lists 6 possible causes. I summarize here - see the full article for more details.

• Indexer cannot find or load filter component or words.
• A component, such as a word breaker or filter, fails and returns an error to the pointer.
• The full-text index exceeds the limit on the number of lines that can be contained in the full-text catalog.
• The clustered index or full-text key index in the indexed table is changed, deleted, or rebuilt.
• A hardware failure or disk corruption leads to damage to the full-text catalog.
• A group of files that contains a full-text table is sent offline or read-only.

The conclusion of the article:

You should look at the crawl log at the end of any meaningful full-text index indexing operation, or when you find that the population is not populated.