Thursday, 5 November 2009

Fulltext catalogues

To identify the full text catalogues run the following SQL:
SELECT name 
FROM sys.fulltext_catalogs;
Alternatively use the SQL Management Studio.
  • Go to Object Explorer and connect to your server.
  • Expand the Databases node and find the database in question.
  • Expand the Storage > Full Text Catalogues node.
  • Right-click on a catalogue name and select Properties.
Note that the Tables/Views page of the Full-Text Catalog Properties dialog box shows how the catalogue tracks changes (radio buttons at the bottom of the screen).

Common tasks

“Tells SQL Server to rebuild the entire catalog. When a catalog is rebuilt, the existing catalog is deleted and a new catalog is created in its place. All the tables that have full-text indexing references are associated with the new catalog. Rebuilding resets the full-text metadata in the database system tables.”
“Tells SQL Server to perform a master merge, which involves merging the smaller indexes created in the process of indexing into one large index. Merging the full-text index fragments can improve performance and free up disk and memory resources. If there are frequent changes to the full-text catalog, use this command periodically to reorganize the full-text catalog.
REORGANIZE also optimizes internal index and catalog structures.
Keep in mind that, depending on the amount of indexed data, a master merge may take some time to complete. Master merging a large amount of data can create a long running transaction, delaying truncation of the transaction log during checkpoint. In this case, the transaction log might grow significantly under the full recovery model. As a best practice, ensure that your transaction log contains sufficient space for a long-running transaction before reorganizing a large full-text index in a database that uses the full recovery model. For more information, see Managing the Size of the Transaction Log File.”

SQL command to run

You can trigger a rebuild or reorganisation using the following command: