Thursday, 5 November 2009

Fulltext catalogues

To identify the full text catalogues run the following SQL:
SELECT name 
FROM sys.fulltext_catalogs;
GO
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

Rebuild
“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.”
Reorganize
“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:
ALTER FULLTEXT CATALOG catalog_name 
{ REBUILD [ WITH ACCENT_SENSITIVITY = { ON | OFF } ]
| REORGANIZE
| AS DEFAULT 
}
See http://msdn.microsoft.com/en-us/library/ms176095.aspx