Warning: Wordbreaker, filter or protocol handler used by catalog ‘foo’ does not exist.

While monitoring database restores in the DR environment, as you should do, I noticed the following warning message:

Warning: Wordbreaker, filter, or protocol handler used by catalog ‘foo_FullText’ does not exist on this instance. Use sp_help_fulltext_catalog_components and sp_help_fulltext_system_components check for mismatching components. Rebuild catalog is recommended.

As this is a learning exercise for me, I’m going to look at options to rebuild, and diagnosing the problem. Google brought back very few useful links.

First, for those of you in a hurry, I’ll show how to rebuild Full Text Catalogs. After that, I’ll look at how you can diagnose these issues and identify the cause.

Rebuilding Full Text Catalogs

Rebuild Full Text Catalog using SSMS

There is a recommendation within the message to rebuild the full text catalog, which is easy enough. In SSMS’s Object Explorer, navigate to the database affected, and expand the Storage / Full Text Catalog nodes:

This brings up a progress box and a confirmation box on top of that:

Click on “OK”, and you’ll hopefully end up with success:

Rebuild Full Text Catalog using T-SQL

It’s easy enough to do this using the ALTER FULLTEXT CATALOG command:

USE foo_DB
GO
ALTER FULLTEXT CATALOG foo_FullText REBUILD

However, what actually has caused the problem?

Reminder: I’m getting this problem when taking a database backup from a production environment and restoring it into the DR environment.

The warning message does provide a suggestion to help you figure out what’s going on. Translating that into T-SQL yields the following:

USE foo_DB -- change this for your database name
GO
exec sp_help_fulltext_catalog_components
exec sp_help_fulltext_system_components 'all'

sp_help_fulltext_catalog_components gives an overview of the filters installed. Note that this component is due to be removed in a future release of SQL Server. sp_help_fulltext_system_components gives more detailed information about the individual filters installed.

So now all I have to do is play “spot the difference”. Doing this by eye is going to be painful, not least because of the use of GUIDs – these are not easy for humans to read. So we’ll try scripting it instead.

EXEC sp_configure 'Show Advanced Options', 1
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE WITH OVERRIDE
GO

USE tempdb
GO

IF NOT OBJECT_ID('sphFTCC') IS NULL 
    DROP TABLE sphFTCC
   
IF NOT OBJECT_ID('sphFTSC') IS NULL 
    DROP TABLE sphFTSC
GO       

CREATE TABLE sphFTCC
    (
      FullTextCatalogName SYSNAME ,
      FullTextCatalogID INT ,
      ComponentType SYSNAME ,
      ComponentName SYSNAME ,
      ClsId UNIQUEIDENTIFIER ,
      FullPath NVARCHAR(256) ,
      [Version] NVARCHAR(30) ,
      Manufacturer SYSNAME ,
      ServerName SYSNAME NULL
    )

CREATE TABLE sphFTSC
    (
      ComponentType SYSNAME ,
      ComponentName SYSNAME ,
      ClsId UNIQUEIDENTIFIER ,
      FullPath NVARCHAR(256) ,
      [Version] NVARCHAR(30) ,
      Manufacturer SYSNAME ,
      ServerName SYSNAME NULL
    )
GO

INSERT  INTO sphFTCC
        ( FullTextCatalogName ,
          FullTextCatalogID ,
          ComponentType ,
          ComponentName ,
          ClsId ,
          FullPath ,
          [Version] ,
          Manufacturer
        )
        SELECT  *
        FROM    OPENROWSET('SQLNCLI',
                           'Server=<SourceServerName>;Trusted_Connection=yes;Database=<SourceDBName>;',
                           'exec sp_help_fulltext_catalog_components') AS a
UPDATE  sphFTCC
SET     ServerName = '<SourceServerName>'
WHERE   ServerName IS NULL

INSERT  INTO sphFTCC
        ( FullTextCatalogName ,
          FullTextCatalogID ,
          ComponentType ,
          ComponentName ,
          ClsId ,
          FullPath ,
          [Version] ,
          Manufacturer
        )
        SELECT  *
        FROM    OPENROWSET('SQLNCLI',
                           'Server=<DestinationServerName>;Trusted_Connection=yes;Database=<DestinationDBName>;',
                           'exec sp_help_fulltext_catalog_components') AS a
UPDATE  sphFTCC
SET     ServerName = '<DestinationServerName>'
WHERE   ServerName IS NULL

/* Have to use "SET NOCOUNT ON" with the next part, because of intermediate results within sp_help_fulltext_system_components  */
INSERT  INTO sphFTSC
        ( ComponentType ,
          ComponentName ,
          ClsId ,
          FullPath ,
          [Version] ,
          Manufacturer
        )
        SELECT  *
        FROM    OPENROWSET('SQLNCLI',
                           'Server=<SourceServerName>;Trusted_Connection=yes;Database=<SourceDBName>;',
                           'SET NOCOUNT ON; exec sp_help_fulltext_system_components ''all''')
                AS a
UPDATE  sphFTSC
SET     ServerName = '<SourceServerName>'
WHERE   ServerName IS NULL


INSERT  INTO sphFTSC
        ( ComponentType ,
          ComponentName ,
          ClsId ,
          FullPath ,
          [Version] ,
          Manufacturer
        )
        SELECT  *
        FROM    OPENROWSET('SQLNCLI',
                           'Server=<DestinationServerName>;Trusted_Connection=yes;Database=<DestinationDBName>;',
                           'SET NOCOUNT ON; exec sp_help_fulltext_system_components ''all''')
                AS a
UPDATE  sphFTSC
SET     ServerName = '<DestinationServerName>'
WHERE   ServerName IS NULL

;
WITH    C3Source
          AS ( SELECT   ComponentType ,
                        ComponentName ,
                        ClsId ,
                        FullPath ,
                        [Version] ,
                        Manufacturer
               FROM     sphFTSC
               WHERE    ServerName = '<SourceServerName>'
             ),
        C3Dest
          AS ( SELECT   ComponentType ,
                        ComponentName ,
                        ClsId ,
                        FullPath ,
                        [Version] ,
                        Manufacturer
               FROM     sphFTSC
               WHERE    ServerName = '<DestinationServerName>'
             )
    SELECT  C3Source.ClsId AS Source_ClsId,
            C3Source.Version AS Source_Version,
            ISNULL(C3Source.ComponentType, C3Dest.ComponentType) AS ComponentType ,
            ISNULL(C3Source.ComponentName, C3Dest.ComponentName) AS ComponentName ,
            C3Dest.Version AS Dest_Version ,
            C3Dest.ClsId AS Dest_ClsId
    FROM    C3Source
            FULL OUTER JOIN C3Dest ON C3Source.ComponentType = C3Dest.ComponentType
                                    AND C3Source.ComponentName = C3Dest.ComponentName
    WHERE   C3Source.ClsId <> C3Dest.ClsId
            OR C3Prod.Version <> C3Dest.Version

So now we’ve got our output – a list of ClsIds & Versions for mis-matched components, showing Source components on the left and Destination components on the right. Closer examination of the sphFTSC table will enable you to find out more information about the different components. If one of the servers is missing a component that the other one has, that’ll show as NULL in the appropriate place.

In my case, it’s easy enough to explain the problem – I have (for some reason) a more recent Service Pack on the DR server than I have on the Production server! Oops. Not sure how that happened, but I’m pretty sure that I didn’t build either of those servers… Still, given the dataset in question is small, and it’s a quick fix if necessary, I’ll just make a note in the DR documentation.

Advertisements
This entry was posted in SQLServerPedia Syndication and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s