FULL-TEXT (FT), SQL Server, Index Blocking, SQL Server

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