Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

Next Webcast - Manage and Monitor SQL Server - Lots of demos!
 

Script to Distribute Existing SQL Server Full Text Indexes Across Multiple Catalogs


By:   |   Read Comments   |   Related Tips: More > Full Text Search

Problem

There is a SQL Server database that has Full Text enabled, but there have been multiple problems with it:

  • Sometimes we see a lot of messages in the error log with the text: “Warning Master Merge operation was not done for dbid 5, objid 123456789, so querying index will be slow. Please run alter full text catalog reorganize”. The database ID is the same for all of them, only the object ID is different.
  • Sometimes the users are unable to connect to this database, and we’re only able to connect using the dedicated administrator connection.
  • When the database needs to be refreshed, the following command never ends: “ALTER DATABASE [db] SET SINGLE_USER WITH ROLLBACK IMMEDIATE”. When we check the active sessions, nothing is using this database.
  • When the database needs to be dropped, we get the following error: “Cannot drop database [db] because it is currently in use”, and if we check the active sessions during the database drop, there is one with the following wait info: “(100799524ms) FT_MASTER_MERGE”.
  • The only way to drop the database is restarting the SQL Server service.

After doing our research, we found the database has 161 full text indexes on a single catalog and this was the problem.

Solution

TThere are multiple recommendations on the internet regarding SQL Server Full Text Indexes:

  • You must not rebuild your full text indexes at the same time, as this will consume all available threads. Source
  • YYou must have a maximum of 7 full text indexes per catalog, and large tables should have their own catalog. Source
  • You must have tables with similar update characteristics on the same catalog. Source
  • You can’t disable full text search on a database. Source

Because this database has 161 full text indexes, we took care of the distribution by spreading them across different catalogs without going over 7 tables per catalog.

SQL Server Script to Distribute Full Text Indexes to Multiple Catalogs

TTo help me with the process, I created the following script. The script creates output that will perform the distribution of the full text indexes across different catalogs. Note that you must change SSMS from “Results to Grid” to “Results to Text”, and replace “[db]” with the name of your database. Also, this script only works if you have one catalog with multiple indexes and none of them have been created with the “TYPE COLUMN” parameter.

USE [db]GO
 
SESET NOCOUNT ON
 
DECLARE @IndexesCount INT, @RequiredCatalogs INT, @CatalogName VARCHAR(128)SET @IndexesCount = (SELECT COUNT(1) FROM [sys].[fulltext_indexes])
SET @RequiredCatalogs = (@IndexesCount/7)+1
SET @CatalogName = (SELECT [name] FROM [sys].[fulltext_catalogs])
 
PRINT '/*'
SELECT 'Number of indexes' [Message], @IndexesCount [Count]
PRINT '*/' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
 
-- Data
CREATE TABLE #Info ([Id] INT IDENTITY(0, 1), [Table] VARCHAR(128), [Index] VARCHAR(128), [Columns] VARCHAR(MAX))
INSERT INTO #Info ([Table], [Index], [Columns])
SELECT [t].[name] [Table], [i].[name] [Index],
       STUFF((SELECT (', [' + [c].[name] + ']')
               FROM [sys].[fulltext_index_columns] [ic]
               INNER JOIN [sys].[columns] [c] ON [c].[object_id] = [ic].[object_id] AND [c].[column_id] = [ic].[column_id]
               WHERE [ic].[object_id] = [fi].[object_id]
               FOR XML PATH('')), 1, 2, '') [Columns]
FROM [sys].[fulltext_indexes] [fi]
INNER JOIN [sys].[tables] [t] ON [t].[object_id] = [fi].[object_id]
INNER JOIN [sys].[indexes] [i] ON [i].[object_id] = [fi].[object_id] AND [i].[index_id] = [fi].[unique_index_id]
ORDER BY [t].[name]
 
-- Drop fulltext indexes
SELECT 'ALTER FULLTEXT INDEX ON [' + [Table] + '] DROP (' + [Columns] + ');' [-- Drop fulltext indexes]
  FROM #Info [i]
SELECT 'EXEC sp_fulltext_table @tabname=''[' + [Table] + ']'', @action=''deactivate'';' [-- Drop fulltext indexes]
  FROM #Info [i]
SELECT 'DROP FULLTEXT INDEX ON [' + [Table] + '];' [-- Drop fulltext indexes]
  FROM #Info [i]
 
-- Drop old catalog
SELECT 'DROP FULLTEXT CATALOG ['[email protected]+'];' [-- Drop old catalog]
 
-- Create new catalogs
;WITH [mycte] AS (
   SELECT 1 [DataValue]
   UNION ALL
   SELECT [DataValue] + 1
     FROM [mycte]
    WHERE [DataValue] + 1 <= @RequiredCatalogs)
SELECT 'CREATE FULLTEXT CATALOG [' + @CatalogName + CAST([DataValue] AS VARCHAR)
       + '] WITH ACCENT_SENSITIVITY = ON AUTHORIZATION [dbo];' [-- Create new catalogs]
  FROM [mycte]
OPTION (MAXRECURSION 0)
 
-- Create fulltext indexes
SELECT 'CREATE FULLTEXT INDEX ON [' + [Table] + '] (' + [Columns]
      + ') KEY INDEX [' + [Index] + '] ON [' + @CatalogName
      + CAST(([Id]%@RequiredCatalogs) + 1 AS VARCHAR) + '];' [-- Create fulltext indexes]
  FROM #Info [i]
 
DROP TABLE #Info

The information stored in the table #Info is similar to this:

Table Index Columns
Tbl1 Tbl1_ndx [description]
Tbl2 Tbl2_ndx [description]
Tbl3 Tbl3_ndx [description], [start], [end]

The output of the script is similar to this:

/*
Message           Count
----------------- -----------
Number of indexes 161
 
*/
 
-- Drop fulltext indexes
---------------------------------------------------------------------------------------------------
ALTER FULLTEXT INDEX ON [tbl1] DROP ([description]);
ALTER FULLTEXT INDEX ON [tbl2] DROP ([description]);
ALTER FULLTEXT INDEX ON [tbl3] DROP ([description], [start], [end]);
 
-- Drop fulltext indexes
---------------------------------------------------------------------------------------------------
EXEC sp_fulltext_table @tabname='[tbl1]', @action='deactivate';
EXEC sp_fulltext_table @tabname='[tbl2]', @action='deactivate';
EXEC sp_fulltext_table @tabname='[tbl3]', @action='deactivate';
 
-- Drop fulltext indexes
---------------------------------------------------------------------------------------------------
DROP FULLTEXT INDEX ON [tbl1];
DROP FULLTEXT INDEX ON [tbl2];
DROP FULLTEXT INDEX ON [tbl3];
 
-- Drop old catalog
---------------------------------------------------------------------------------------------------
DROP FULLTEXT CATALOG [catalog];
 
-- Create new catalogs
---------------------------------------------------------------------------------------------------
CREATE FULLTEXT CATALOG [catalog1] WITH ACCENT_SENSITIVITY = ON AUTHORIZATION [dbo];
CREATE FULLTEXT CATALOG [catalog2] WITH ACCENT_SENSITIVITY = ON AUTHORIZATION [dbo];
CREATE FULLTEXT CATALOG [catalog3] WITH ACCENT_SENSITIVITY = ON AUTHORIZATION [dbo];
 
-- Create fulltext indexes
---------------------------------------------------------------------------------------------------
CREATE FULLTEXT INDEX ON [tbl1] ([description]) KEY INDEX [tbl1_ndx] ON [catalog1];
CREATE FULLTEXT INDEX ON [tbl2] ([description]) KEY INDEX [tbl2_ndx] ON [catalog2];
CREATE FULLTEXT INDEX ON [tbl3] ([description], [start], [end]) KEY INDEX [tbl3_ndx] ON [catalog3];	
	

Now this script can be copied and run on the same database and the full text indexes are going to be distributed across different catalogs. This will ease the administration of the full text indexes and will minimize the issues related to having too many full text indexes on big tables on the same catalog.

Next Steps
  • You can find more information about creating full text indexes here.
  • You can find more full text search tips here.


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Pablo Echeverria I've worked for more than 10 years as a software programmer and analyst. Last year I switched jobs to a DBA position, where I've been suited to implement new processes and optimize existing ones.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools