SQL Server

Troubleshooting SQL Server 2016 full-text search

Troubleshooting SQL Server 2016 full-text search

If you have problems with SQL Server 2016 full-text search, review this troubleshooting
checklist to find potential solutions.

1. Install the latest SQL Server 2016 service pack.

Because some SQL Server 2016 full-text search bugs were fixed in SQL Server service
packs, you should install the latest SQL Server service pack.
At the time this article was written the latest SQL Server 2016 service pack was
service pack 1. You can download the SQL Server 2016 service pack 1 at here:
https://support.microsoft.com/en-us/kb/3182545

2. Ensure the user needs to create, alter or drop the full-text catalogs have
appropriate permissions.

To create a full-text catalog the user must have CREATE FULLTEXT CATALOG permission
on the database, or must be a member of the db_owner, or db_ddladmin fixed database
roles. To alter a full-text catalog the user must have ALTER permission on the
full-text catalog, or be a member of the db_owner, db_ddladmin fixed database roles,
or sysadmin fixed server role. To drop a full-text catalog the user must have DROP
permission on the full-text catalog or be a member of the db_owner, or db_ddladmin
fixed database roles.

3. 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 full-text query fail 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).

4. Ensure the user needs to create, alter or drop the full-text indexes have
appropriate permissions.

To create a full-text index the user must have REFERENCES permission on the
full-text catalog and have ALTER permission on the table of view, or be a member
of the sysadmin fixed server role, or db_owner, or db_ddladmin fixed database
roles. To alter or drop a full-text index the user must have ALTER permission
on the table or view, or be a member of the sysadmin fixed server role, or the
db_ddladmin or db_owner fixed database roles.

5. If you encountered error indicating that full-text query contain only ignored
words try to rewrite this query to a phrase-based query, removing the noise words.

You will get the error indicating that full-text query contains ignored words when
CONTAINS predicate is used with words such ‘OR’, ‘AND’ and ‘BETWEEN’ as searchable
phrase. For example, this select statement returns error:
SELECT ProductName FROM Products WHERE CONTAINS(ProductName, ‘and OR between’)

6. When you start a full or incremental full-text population, the catalogs
are not populated.

This problem occurs when the BUILTINAdministrators login was removed from
SQL Server or when the Microsoft Search service is not running under the Local
System account. To work around this problem, you can set the Microsoft Search
service to run under the Local System account or add BUILTINAdministrators as
a member of the sysadmin server role.

7. If you encountered error indicating that insufficient memory available, set
the virtual memory setting to an amount equal to 3 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 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).

8. The error 7601 may occur when you use the CONTAINS or FREETEXT predicate.

This is the error message text: “Cannot use a CONTAINS or FREETEXT predicate
on %S_MSG ‘%.*ls’ because it is not full-text indexed.” To work around this
problem, you should create a full-text index for the table.

9. You can get the error 7614 when you try to alter or drop the table’s column.

This error indicates that you cannot alter or drop column because it is enabled
for Full-Text Search. To work around this problem, you should disable the Full-Text
Search for this column.

10. The error 7624 may occur when you use full-text catalog.

This is the error message text: “Full-text catalog ‘%ls’ is in an unusable state”.
To resolve this problem, you should drop and re-create this full-text catalog.

11. You can get the error 7642 when you try to create a full-text catalog.

This error indicates that a full-text catalog with this name already exists in
this database. To work around this problem, you can use a different name for a
full-text catalog.

12. The full-text search feature does not always return the expected results.

For example, when you search for words by using inflections, different results
might be returned. This bug was fixed in Cumulative update package 1 for
SQL Server 2016. You can download the Cumulative Update package 1 for
SQL Server 2016 at here:
https://support.microsoft.com/en-us/help/3164674/cumulative-update-1-for-sql-server-2016

13. Incorrect full-text keys are recorded for the rows that aren’t indexed
correctly by a full-text index.

This problem occurs when you create a primary key on a column which includes
large decimal or numeric values and then you create a full-text index by using
this column as the unique key index. This bug was fixed in Cumulative update
package 4 for SQL Server 2016. You can download the Cumulative Update package 4
for SQL Server 2016 at here:
https://support.microsoft.com/en-us/help/3205052/cumulative-update-4-for-sql-server-2016
To work around this problem, you can add a unique bigint or int column to the table
and specify full-text by using that column instead.

14. The full-text queries may return incorrect results when documents are too
large for Full-Text Search indexing.

This bug was fixed in Cumulative update package 5 for SQL Server 2016. You can
download the Cumulative Update package 5 for SQL Server 2016 at here:
https://support.microsoft.com/en-us/help/4013105/cumulative-update-5-for-sql-server-2016
After you install this update and SQL Server indexes documents that are too large
for Full-Text Search indexing, the documents are skipped instead of throwing
exceptions and generating file dumps.

15. You receive incorrect results when you use SQL Server Management Objects (SMO)
to generate a script for the full-text Search index.

This problem occurs when you configure the replication to replicate the full-text
search index. This bug was fixed in Cumulative update package 6 for SQL Server 2016.
You can download the Cumulative Update package 6 for SQL Server 2016 at here:
https://support.microsoft.com/en-us/help/4019914/cumulative-update-6-for-sql-server-2016

Facebooktwittergoogle_plusredditpinterestlinkedinmail