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

In this example, you perform typical investigation and clean-up tasks. Assume that you have already connected to the pubs database, the full-text service has been started, and that you are working with the fictitious writers and books tables. For a description of the tables, see Administering Full-Text Features Using Stored Procedures and Scalar Functions_administering_full-text_features_using_stored_procedures_and_scalar_functions

1. Obtain a list of all the full-text catalogs linked to the pubs database by executing this stored procedure:

   sp_help_fulltext_catalogs 
   

   For more information, see sp_help_fulltext_catalogs

   Because the pubs database is the current database, this stored procedure returns the following meta data for all the full-text catalogs linked to the pubs database:

   • Name and integer identifier of the full-text catalog
     
     
   • Full-text catalog root directory
     
     
   • Full-text catalog population status
     
     
   • Number of tables linked to this full-text catalog

   A variation of this stored procedure in which a full-text catalog name parameter is specified returns this information for a single full-text catalog.

2. Obtain a list of all the tables in the database that have been enabled for full-text processing by executing this stored procedure:

sp_help_fulltext_tables

For more information, see sp_help_fulltext_tables

This stored procedure returns the following meta data for each table:

• The two-part name of the table
  
  
• The integer identifier of the column used as the table's full-text key
  
  
• The name of the index that is used to impose a unique constraint on the full-text key column
  
  
• The full-text status of the table
  
  
• The name of the full-text catalog of the table

  Two other variations of this stored procedure are supported. If the fulltext_catalog_name parameter is specified, this information is returned for all the tables linked to that full-text catalog. If both the catalog_name and table_name parameters are specified, or if just the table_name parameter is specified, then this information is returned for that table.

3. Obtain a list of all the columns in the database that have been enabled for full-text processing by executing this stored procedure:

   sp_help_fulltext_columns 
   

   For more information, see sp_help_fulltext_columns

   This stored procedure returns the following meta data about each column:

   • The two-part name of the table in the column
     
     
   • The name and integer identifier of the column

   A variation of this stored procedure, in which a table name parameter is specified, returns this information for a single table.

   The compiled lists indicate some issues. The mycatalog full-text catalog is no longer used except by the mytable table, which no longer has any full-text columns that can be queried.

4. Unregister the mytable table for full-text processing by executing this stored procedure:

   sp_fulltext_table  'MyTable', 'drop'
   

   For more information, see sp_fulltext_table.

   This drops the meta data about full-text indexing for the 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. For more information, see sp_fulltext_table.

5. Drop the mycatalog full-text catalog from the file system and its meta data from the system tables by executing this stored procedure:

   sp_fulltext_catalog  'MyCatalogue', 'drop'  
   

   For more information, see sp_fulltext_catalog.

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

   There is at least one full-text catalog in the file system that no longer has corresponding SQL Server meta data. The usual cause of this is the removal of a database.

6. Remove from the file system all full-text catalogs that no longer have meta data for them in SQL Server by executing this stored procedure:

   sp_fulltext_service  'Clean_Up'
   

   For more information, see sp_fulltext_service.

   The structure of the MixedUpCtlg full-text catalog does not match the meta data currently recorded for it in SQL Server. This can occur when the full-text catalog is being dropped, or the database is being dropped and the Microsoft Search service is not running. The drop action changes the meta data related to the full-text catalogs, but is unable to complete the operation because the Microsoft Search service is not running. This leads to inconsistency between the full-text meta data in SQL Server and the associated physical full-text catalog in the file system. This inconsistency can be corrected by invoking the clean-up action on sp_fulltext_service. (Microsoft Search service must be running.)

7. Rebuild, but do not repopulate, the MixedUpCtlg full-text catalog by executing this stored procedure: 

   sp_fulltext_catalog  'MixedUpCtlg',  'Rebuild'
   

   For more information, see sp_fulltext_catalog.

   The sp_fulltext_database stored procedure with the ENABLE option may be used to rebuild all known full-text catalogs.

8. Start a full population of the MixedUpCtlg full-text catalog by executing this stored procedure:

   sp_fulltext_catalog  'MixedUpCtlg',  'start_full'
    

   Note  Full-text catalogs can be created, dropped, and modified as needed; however, avoid making schema changes on multiple catalogs at the same time.