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
- 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
For 20+ years, MSSQLTips.com has been delivering comprehensive technical solutions on a daily basis for the Microsoft SQL Server platform. In that time, an innovative team of over 300 authors from around the globe have delivered over 7000 articles and 1000 webcasts ranging from beginner topics to advanced solutions. Our community prides itself on delivering human only generated content.