Full text index requires dropping and recreating - why?
- by Amjid Qureshi
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
