Export (0) Print
Expand All
1 out of 1 rated this helpful - Rate this topic

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

SQL Server 2000

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.

  1. 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.

  2. 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.

  3. 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.

  4. 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.)

  5. 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.

  6. 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.

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.