Troubleshooting Full-text Search Problems

August 13, 2002


Should you have problems with full-text search, review this troubleshooting checklist to find potential solutions.


1. Check the hardware requirements.

For SQL Server 7.0, you should have:

  • Alpha AXP, Intel or compatible platform
  • Pentium 166 MHz or higher
  • 64MB RAM or more (recommended)
  • 180MB hard disk space

For SQL Server 2000, you should have:

  • Intel or compatible platform
  • Pentium 166 MHz or higher
  • 64MB RAM or more (recommended)
  • 250MB hard disk space


2. Check the software requirements.

To install full-text search, you should have the following operation systems:

  • Windows NT Server version 4.0 (with Service Pack 4 or later for SQL Server 7.0 and Service Pack 5 or later for SQL Server 2000)
  • Windows NT Server Enterprise Edition version 4.0 (with Service Pack 4 or later for SQL Server 7.0 and Service Pack 5 or later for SQL Server 2000)
  • Windows 2000 Server
  • Windows 2000 Advanced Server
  • Windows 2000 DataCenter

Full-text search is supported under the following SQL Server editions:

  • SQL Server 7.0 Standard Edition
  • SQL Server 7.0 Enterprise Edition
  • SQL Server 2000 Personal Edition (except on Windows 9x)
  • SQL Server 2000 Standard Edition
  • SQL Server 2000 Developer Edition
  • SQL Server 2000 Enterprise Edition
  • SQL Server 2000 Enterprise Evaluation Edition


3. If you have problems installing the Full-text Search (MSSearch) service, check the Mssearch.log in the \Temp directory to see if any problems occurred.


4. Verify that both the MSSQLServer and MSSearch services are running.

The full-text search runs as a service named Microsoft Search Service (MSSearch service). So, if this service was not started, the full-text search cannot work.


5. If a full-text catalog population or query fails, check that there is no mismatch of user account information between the MSSQLServer and MSSearch services.

Change the password for the MSSQLServer service using the SQL Server Enterprise Manager (do not use Services in Control Panel to change user account information). Changing the password for the MSSQLServer service results in an update of the account the MSSearch service runs under.


6. Verify that the MSSearch service runs under the local system account.

The Microsoft Search service is assigned to the local system account during full-text search installation. Do not change the MSSearch service account information after the installation. Otherwise, it cannot keep track of the MSSQLServer service account.


7. Verify whether you have a UNC path specification in your PATH variable.

Having the UNC path specification(s) in the SYSTEM or USER PATH variables can result in a full-text query failure with the message that full-text catalog is not yet ready for queries. To work around this, you should replace the UNC path(s) with remapped drive(s) or add the location \%SYSTEMDRIVE%\Program Files\Common Files\SYSTEM\ContentIndex in front of any UNC path specification in the SYSTEM path.


8. If you encountered error indicating that insufficient memory is available, set the virtual memory setting to an amount equal to three times the physical memory and set the SQL Server 'max server memory' server configuration option to 1.5 times the physical memory.

Because working with full-text search is very resource expensive, you should make sure you have enough physical and virtual memory. Set the virtual memory size to at least 3 times the physical memory installed in the computer and set the SQL Server 'max server memory' server configuration option to half the virtual memory size setting (1.5 times the physical memory).


9. If you encountered an error indicating that your full-text query contains only ignored words, try to rewrite the query to a phrase-based query, removing the noise words.

You will get the error indicating that full-text query contains ignored words when the CONTAINS predicate is used with words such as 'OR', 'AND' or 'BETWEEN' as searchable phrase. For example, this select statement returns an error:

SELECT ProductName FROM Products WHERE CONTAINS(ProductName, 'and OR between')


10. Rewrite the English Query's questions, so that these questions will not require a full-text search on a table with a uniqueidentifier key.

Asking the English Query's questions that require a full-text search on a table with a uniqueidentifier key may cause English Query to stop responding.


11. If you decide to install the full-text search by using the BackOffice 4.5 custom setup, after a successful installation of SQL Server 7.0 (without installing full-text search), you should run Setupsql.exe from the BackOffice CD-ROM(2) (\SQL70\Machine_platform\Setup\Setupsql.exe)

You are not allowed to install it by using the BackOffice 4.5 setup, because the BackOffice Custom Installation dialog box falsely indicates that the full-text search has been installed already.


12. If you encountered an error indicating that the full-text query timed out trying to reduce the size of the result set, increase the 'remote query timeout' setting or insert the full-text query result set into a temporary table instead of streaming the results directly to the client.


13. Make a single column unique index for the table you want to be used in a full-text query.

The full-text indexing cannot work on a table that has a unique index on multiple columns. If the table you want to be used in a full-text query does not currently have a single column unique index, add an IDENTITY column with a UNIQUE index or constraint.


14. Upgrade to SQL Server 2000 if you need to work with full-text search in a clustered environment. The full text search is not available in SQL Server 7.0 clustered environments.


» See All Articles by Columnist Alexander Chigrik









The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers