Share via

  • Article

Troubleshooting: Performing Investigation and Clean-up Tasks for Full-Text Catalogs

In this example, you perform typical investigation and clean-up tasks. Assume that you are connected to the AdventureWorks database, you are working with the Document table, and that AdvDocFTCat is the full-text catalog associated with the Document table.

Important

For a SQL Server 2008 database, a full-text catalog is a logical concept that refers to a group of full-text indexes. The full-text catalog is a virtual object that does not belong to any filegroup.

The investigation and clean-up tasks are as follows:

  1. Obtain a list of all the full-text catalogs linked to the AdventureWorks database by running this query on the sys.fulltext_catalogs (Transact-SQL) catalog view:

    SELECT name 
FROM sys.fulltext_catalogs;
GO

    Because the AdventureWorks database is the current database, this returns metadata for all the full-text catalogs linked to the AdventureWorks database.

  2. To get list of indexes in the database use the sys.fulltext_indexes catalog view.

  3. Unregister the Document table for full-text processing by running the following statement:

    DROP FULLTEXT INDEX ON Production.Document;
GO

    For more information, see DROP FULLTEXT INDEX (Transact-SQL).

    This drops the metadata about full-text indexing for the Document table. The existing full-text index remains in place until the next full population or until the full-text catalog is dropped. However, it remains unused.

  4. Drop the AdvDocFTCat full-text catalog from the file system and its metadata from the catalog views by running the following statement:

    DROP FULLTEXT CATALOG AdvDocFTCat;
GO

    For more information, see DROP FULLTEXT CATALOG (Transact-SQL).

    You must complete Step 4 before a full-text catalog can be dropped because its full-text catalog metadata must be updated to remove all full-text indexes.