Troubleshooting SQL Server 2014 full-text search
If you have problems with SQL Server 2014 full-text search, review this troubleshooting
checklist to find potential solutions.
1. Install the latest SQL Server 2014 service pack.
Because some SQL Server 2014 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 2014 service pack was
service pack 1. You can download the SQL Server 2014 service pack 1 at here:
https://www.microsoft.com/en-us/download/details.aspx?id=46694
2. 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).
3. 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.
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. 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).
7. 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.
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. When you execute a full-text query with parallel execution plan, the query result
may be incorrect.
This problem occurs when you create a workload group and set the value of the “Degree of
Parallelism” option to 1 and then execute a full-text query that uses a MAXDOP query hint
that the value is larger than 1. This bug was first fixed in Cumulative Update package 9
for SQL Server 2014. You can download the Cumulative Update package 9 for SQL Server 2014
at here:
https://support.microsoft.com/en-us/kb/3075949