Troubleshooting Full-text Search Problems


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 FilesCommon FilesSYSTEMContentIndex
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)
(SQL70Machine_platformSetupSetupsql.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

Alexander Chigrik
Alexander Chigrik
I am the owner of MSSQLCity.Com - a site dedicated to providing useful information for IT professionals using Microsoft SQL Server. This site contains SQL Server Articles, FAQs, Scripts, Tips and Test Exams.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles