Scenario B: Performing Investigation and Clean-up Tasks for Full-text Catalogs

In this scenario, 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 some tables have been added to that database.

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

   sp_help_fulltext_catalogs

     

   Because the pubs database is the current database, this stored procedure returns the following metadata 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

     

   This stored procedure returns the following metadata for each table:

   • The table’s two-part name
   • 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 table’s full-text status
   • The name of the table’s full-text catalog

   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 parameter and the table_name parameter 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

     

   This stored procedure returns the following metadata 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. Deregister the mytable table for full-text processing by executing this stored procedure:

   sp_fulltext_table  'MyTable', 'drop'

     

   This drops the metadata 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.

5. Drop the mycatalog full-text catalog from the file system and its metadata from the database’s system tables. This is done by executing this stored procedure:

   sp_fulltext_catalog  'MyCatalogue', 'drop' 

     

   It is necessary to carry out Step 4 before a full-text catalog can be dropped because its text-catalog metadata 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 Microsoft® SQL Server™ metadata. 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 metadata for them in SQL Server by executing this stored procedure:

   sp_fulltext_service  'Clean_Up'

     

   The structure of the MixedUpCtlg full-text catalog does not match the metadata 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 metadata 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 metadata 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'

     

   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'