PRB: A Full-Text Search Query on TEXT or NTEXT Column Does Not Return Valid Results (241113)

Notice

IMPORTANT: This article contains information about modifying the registry. Before you modify the registry, make sure to back it up and make sure that you understand how to restore the registry if a problem occurs. For information about how to back up, restore, and edit the registry, click the following article number to view the article in the Microsoft Knowledge Base:

256986 Description of the Microsoft Windows Registry

SYMPTOMS

When you run a full-text CONTAINS query on a full-text enabled text column that has more than 300 KB of text (more than 150 KB of UNICODE text for an ntext column ), you may receive a "0 rows affected" message even though valid results are present.

CAUSE

The Microsoft Search service (MSSearch.exe) maintains the full-text catalogs for SQL Server 7.0 full-text search to search character-based data types such as char, varchar, text and ntext. Columns that you define as text can have more than 300 KB of data (ntext can have more than 150 KB of UNICODE text). When the full-text index is populated, MSSearch service retrieves all the distinct words from the text column and stores them in the virtual memory before it merges them into the master index. The MSSearch property prevents the MSSearch service from consuming all the virtual memory. The MSSearch property is controlled by the following registry and its value: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\Indexer\SQLServer\ <Full_Text_Catalog_Name>\ MaxBytesPerFileFor example: Full_Text_Catalog_Name = SQL0000500005

The default value of this registry key is set to 307200 (300 KB). As a result, MSSearch service assumes 300 KB of text as significant and ignores the remaining data of the text column while it populates the full-text index.

Therefore, when you run a full-text CONTAINS clause query to search a word that is present in the text column after 300 KB of text, you may not obtain valid results.

WORKAROUND

WARNING: If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.

To work around this problem:

On the Start menu, click Run.
Type regedit, and then click OK.
Locate the following key in Registry Editor: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\Indexer\SQLServer\Full_Text_Catalog_Name\(for example: Full_Text_Catalog_Name = SQL0000500005).
Click the MaxBytesPerFile key of type REG_DWORD.
On the Edit menu, click Modify, and then specify the Value Data of the registry key based on your requirement.
Quit Registry Editor.
Rebuild the full-text catalog.

MORE INFORMATION

Steps to Reproduce the Behavior

Add a unique word to the end of a text column. Use a word that has a text size greater than 300 KB in each row (for ntext, the text size is greater than 150 KB).
Enable the column for full-text indexing.
Populate the full-text catalog.
Run the full-text CONTAINS clause query on the column to search the unique word that you added in step 1.