Configuration Information Locations for Full Text Indexes in SQL 2000

By:   |   Comments   |   Related: > Full Text Search


Information about the status of your full-text search process is not always readily available or in one place. Although you can check some values in the Support section of Enterprise Manager, not every configuration value is there. In order to check if your full-text environment is configured correctly, you need to know where to look for this information.


Full-text catalog and configuration information is located in four areas: FULLTEXTSERVICEPROPERTY, DATABASEPROPERTY, FULLTEXTCATALOGPROPERTY, and OBJECTPROPERTY:

FULLTEXTSERVICEPROPERTY (General information on service configuration)
ResourceUsage Regulates the amount of resources used by the full-text service. The range is from 1 (background) to 5 (dedicated), with a default of 3.
ConnectTimeout Indicates the length of time the MSSearch service waits while attempting to connect to the SQL Server. The value is in seconds, with a default of 120. You can modify this number, depending on how busy your server is.
IsFulltextInstalled Indicates whether the Full-Text components are installed. The values are 1 (installed) or not installed (0).
DataTimeout Indicates the length of time the MSSearch will wait while getting data from the SQL Server. This value is in seconds and its default is also 120 seconds. Depending on how busy the server is (resource usage, locks, etc.), you may want to adjust this value. Evidence of the MSSearch service timing out during the crawl will be in the NT Event Logs, with details in Gatherer Logs.
DATABASEPROPERTY (Database configuration information)
Example: SELECT DATABASEPROPERTY('adventureworks2000', 'IsFulltextEnabled')
IsFulltextEnabled Lets you know if Full-Text is enabled on the database
FULLTEXTCATALOGPROPERTY (Specific information on the catalog itself)
PopulateStatus Indicates the current status of population:
0 = Idle
1 = Full population in progress
2 = Paused
3 = Throttled
4 = Recovering
5 = Shutdown
6 = Incremental population in progress
7 = Building index
8 = Disk is full. Paused.
9 = Change tracking
ItemCount Indicates the number of items currently indexed in the full-text catalog
IndexSize Indicates the size, in megabytes, of the index
UniqueKeyCount Indicates the number of unique words in the full-text catalog
LogSize Indicates the size, in bytes, of ALL error logs associated with the specified full-text catalog
PopulateCompletionAge Indicates the number of seconds between 01/01/1990 00:00:00 and completion of last population. Although this number sounds a little strange, you can use dateadd function to find out when the last population was completed.

Example: select dateadd("s", 540573562, '01/01/1990 00:00:00'), which yields 2007-02-17 15:19:22.000-the exact time population of the sample full-text completed.
OBJECTPROPERTY (Specific information on the catalog itself)
Example: SELECT OBJECTPROPERTY(OBJECT_ID('Document'), 'TableFullTextBackgroundUpdateIndexOn')
TableFullTextBackgroundUpdateIndexOn Indicates whether the table is set for having the full-text catalogs populated in the background
TableFulltextCatalogId Shows the catalog number where data from the full-text index resides
TableFullTextChangeTrackingOn Indicates whether change-tracking is enabled on the table
TableFulltextKeyColumn Returns the ID of the column associated with the index specified as the key index for the full-text catalog
TableFullTextPopulateStatus Indicates the current population status of the table
TableHasActiveFulltextIndex Indicates whether the table has an active full-text index

One of the items above, the ItemCount, can be of great benefit if you use change-tracking for population and you have separate tables to house the actual indexes. This number can be compared to the count of records in the index table to make sure population is working properly. In each case, make sure you type the correct name in, otherwise a NULL value is returned (no errors).

If changes are needed to the full-text service or components, there are a number of stored procedures that you can use to change them:

System configuration changes
sp_fulltext_service stored procedure
exec sp_fulltext_service @action = 'action', @value = 'value'
Example: exec sp_fulltext_service @action = 'connect_timeout', @value = '90'

Enable a database for full-text indexing
sp_fulltext_database stored procedure
exec sp_fulltext_database @action = 'action'
Example: use adventureworks2000
exec sp_fulltext_service @action = 'enable'
Create or alter a full-text catalog
sp_fulltext_catalog stored procedure
exec sp_fulltext_catalog @ftcat='catalog_name, @action = 'action', if creating: @path = 'file_path'
Example: use adventureworks2000
exec sp_fulltext_catalog @ftcat = 'ctgDocumentSummary', @action = 'create', @path = 'E:\MSSQL\FTData'

Mark/unmark a table for full-text indexing, or modify change-tracking behavior
sp_fulltext_table stored procedure
exec sp_fulltext_table @tabname='table_name', @action = 'action', @ftcat = 'catalog_name', @keyname = 'name_of_unique_index_in_table'

Example: use adventureworks2000
exec sp_fulltext_table @tabname = 'Document', @action = 'create', @ftcat = 'ctgDocumentSummary', @keyname = 'PK_Document_DocumentID'

Adds/modifies columns associated with a full-text catalog
sp_fulltext_column stored procedure
exec sp_fulltext_table @tabname='table_name', @colname = 'column_name', @action = 'action', (optional: @language = 'language', @type_colname = 'column_name' (used when the data type of the column name is image)

Example: use adventureworks2000
exec sp_fulltext_table @tabname = 'Document', @action = 'create', @ftcat = 'ctgDocumentSummary', @keyname = 'PK_Document_DocumentID'

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author MSSQLTips was started in 2006 to provide SQL Server content about various aspects of SQL Server and other database platforms.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

get free sql tips
agree to terms