Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Configuration Information Locations for Full Text Indexes in SQL 2000


By:   |   Last Updated: 2007-02-19   |   Comments   |   Related Tips: More > Full Text Search

Problem

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.

Solution

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

FULLTEXTSERVICEPROPERTY (General information on service configuration)
Example: SELECT FULLTEXTSERVICEPROPERTY('IsFulltextInstalled')
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


Last Updated: 2007-02-19


next webcast button


next tip button



About the author
MSSQLTips author Edgewood Solutions Edgewood Solutions is a technology company focused on Microsoft SQL Server and founder of MSSQLTips.com.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools