solving sql server problems for millions of dbas and developers since 2006

SQL Product Highlight

Idera - SQL safe backup

Idera's SQL safe provides a high-performance backup and recovery solution for Microsoft SQL Server. SQL safe saves money by reducing database backup time by up to 50% over native backups and reducing backup disk space requirements by up to 95%. SQL safe also enables complete "hands-free" automated backup of your entire SQL Server infrastructure and ensures compliance with your organization's backup and recovery policies. From implementations with tens of SQL servers to enterprises with hundreds of servers spread around the globe, SQL safe is the only SQL Server backup and recovery solution that scales to meet the challenge.

Learn more!

Configuration Information Locations for Full Text Indexes in SQL 2000

By: Edgewood Solutions | Read Comments | Print

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

Related Tips: More

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'

Note: This is also the stored procedure to use to clean up old catalog files. When catalogs are created, dropped, and re-created, the associated file system files are not deleted. Using this stored procedure will clean up all files not associated with a current full-text catalog.

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

Use the locations and values listed above to create your own views and statements to get the information you need
Review information on OBJECTPROPERTY, DATABASEPROPERTY, FULLTEXTSERVICEPROPERTY, and FULLTEXTCATALOGPROPERTY.
Read more Full-Text tips on MSSQLTIPS.com

Related Tips: More | Become a paid author

Last Update: 2/19/2007

Share:

Connect with MSSQLTips.com

Comments and Feedback:

Sponsor Information

"SQL doctor is the best SQL product on the market, by far. All of Idera's tools are great, but this is the icing on the cake!"

It takes just 5 minutes to connect your SQL Databases to source control. Got 5 minutes? Get started now.

What grade do you think your SQL Servers get? Find out with a SQL Server Health Check consultant.

Find and Fix SQL issues with Foglight Performance Analysis. Get a free copy.

Solving SQL Server problems for millions of DBAs and Devs since 2006. Join now.

Free web casts for DBAs and Developers on Performance Tuning, Development, Administration and more....