Databases
StackOverflow2013
Postsdbo
POFull text index requires dropping and recreating - why?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POFull text index requires dropping and recreating - why?
    primarykey
    data
    text
    <p>So I've got a web app running on .net 3.5 connected to a SQL 2005 box.</p> <p>We do scheduled releases every 2 weeks.</p> <p>About 14 tables out of 250 are full text indexed.</p> <p>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.</p> <p>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</p> <p>We have tried just rebuilding the fulltext index but that doesn't fix the issue.</p> <p>My question is why do we have to do this ? what can we do to sort the index out?</p> <p>Here is a bit of the script, </p> <pre><code>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 </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USAmjid Qureshi
    1. USAmjid Qureshi
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. POFull text index requires dropping and recreating - why?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POFull text index requires dropping and recreating - why?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POFull text index requires dropping and recreating - why?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COAre the 14 tables being dropped and reloaded?
      singulars
      1. POFull text index requires dropping and recreating - why?
      1. USRaj Kaimal
    2. CONo, 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
      singulars
      1. POFull text index requires dropping and recreating - why?
      1. USAmjid Qureshi
    3. COIt seems as though you may have some kind of problem
      singulars
      1. POFull text index requires dropping and recreating - why?
      1. USScozzard
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload