FULL-TEXT (FT), SQL Server, Index Blocking, SQL Server (FT_MON, FT_SEARCH, FT_CRAWL)

• jkelly

  Right there with Babe

  Points: 765

  More actions

  March 2, 2018 at 12:58 pm

  #104394

  Context: FULL-TEXT (FT), SQL Server, Index Blocking, SQL Server
  FT_MON, FT_SEARCH, FT_CRAWL
  Resolution:
  a.)    Verify that FT is the issue with sp_who2
  b.)    Put the database in single user mode and drop the index.
  ALTER DATABASE [foobar] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  GO
  DROP FULLTEXT INDEX ON foo;  
  GO
  ALTER DATABASE [foobar] SET MULTI_USER WITH ROLLBACK IMMEDIATE;

  USE [Shared]
  GO

  ALTER
  -- CREATE
  PROC dbo. NoBotFindFullTextIndices (
         @TableName VARCHAR (256) = ''
  ,      @DatabaseName VARCHAR (256) = ''
  ,      @found INT = 0 OUTPUT
  )
  AS
  BEGIN

         /***************************************************************
         Author  
         2018-02-28 15:42:50.917 1968.95

         Source:
         https://stackoverflow.com/questions/16280918/how-to-find-full-text-indexing-on-database-in-sql-server-2008

  Execution:
  DECLARE @foundIt INT = 0
         EXEC [Shared].dbo.sp_NoBotFindFullTextIndices
                'OLS_PRODUCT_SEARCH'
         ,      'SMCO-PERSONIFY-DEV'
         ,      @found = @foundIt OUTPUT

         Note - a simpler way but with less utility ...

         SELECT DISTINCT
      object_name(fic.[object_id])as table_name
  ,   [name]
         FROM   [foobar].sys.fulltext_index_columns fic
  JOIN   [foobar].sys.columns c
             on  c.[object_id] = fic.[object_id]
             and c.[column_id] = fic.[column_id]

         ****************************************************************/

         SET NOCOUNT ON
         SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

         DECLARE @execMe NVARCHAR (2048) = ''
        
         SET @execMe = '
                SELECT COUNT (t.name)
                FROM ['
                + @DatabaseName
                + '].sys.tables t
                JOIN ['
                + @DatabaseName
                + '].sys.fulltext_indexes fi
                       ON     t.[object_id] = fi.[object_id]
                JOIN ['
                + @DatabaseName
                + '].sys.fulltext_index_columns ic
                       ON     ic.[object_id] = t.[object_id]
                JOIN ['
                + @DatabaseName
                + '].sys.columns cl
                       ON     ic.column_id = cl.column_id
                       AND    ic.[object_id] = cl.[object_id]
                JOIN  ['
                + @DatabaseName
                + '].sys.fulltext_catalogs c
                       ON     fi.fulltext_catalog_id = c.fulltext_catalog_id
                JOIN  ['
                + @DatabaseName
                + '].  sys.indexes i
                       ON     fi.unique_index_id = i.index_id
                       AND    fi.[object_id] = i.[object_id]
                WHERE t.name = '''
                + @TableName
                + ''';'

                EXEC @found = sp_executeSQL @execMe
               
  END

  ��s�m/