By: Pablo Echeverria | Updated: 2018-06-11 | Comments | Related: > 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 ['+@CatalogName+'];' [-- 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.
About the author
Pablo Echeverria is a talented database administrator and C#.Net software developer since 2006. Pablo wrote the book "Hands-on data virtualization with Polybase". He is also talented at tuning long-running queries in Oracle and SQL Server, reducing the execution time to milliseconds and the resource usage up to 10%. He loves learning and connecting new technologies providing expert-level insight as well as being proficient with scripting languages like PowerShell and bash. You can find several Oracle-related tips in his LinkedIn profile.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2018-06-11
