INF: Installing SQL Server 2000 Service Pack 3 Rebuilds All Full-Text Catalogs (327217)

SUMMARY

The values returned in the Rank column in full-text search (FTS) queries that use the CONTAINSTABLE or FREETEXTTABLE functions
may change when the catalogs repopulate. The values may change even if the underlying data does not change. To resolve
this ranking inconsistency, Microsoft made a change in the full-text catalog format. As a result, you must upgrade
all full-text catalogs to the new format when you upgrade to SQL Server 2000 Service Pack 3 (SP3).
This article contains information about the setup of SQL Server 2000 SP3 that is specific to full-text search.
It also provides workaround information to minimize any full-text search downtime that may occur
when you upgrade to SQL Server 2000 SP3.

You must rebuild all the full-text catalogs after you upgrade the full-text catalog format. The
catalogs are rebuilt automatically when you start the MSSearch service, after the SQL Server 2000
SP3 setup completes. In the intitial stages of the SQL Server 2000 SP3 setup, a window opens. You
will see a message that states all the full-text catalogs must be rebuilt. For the SQL Server 2000 SP3
setup to continue, you must click to select the Upgrade Microsoft Search and apply
SQL Server 2000 SP3 (REQUIRED) check box.

All the catalogs from all instances of SQL Server 2000 on the same computer will be rebuilt, not just
the catalogs associated with the instance that is being upgraded. The rebuild operation may be time
and resource intensive. As a result, the full-text catalogs are not available until the rebuild completes.

CAUSE

An issue with the MSSearch catalog format causes an inconsistency in the rank values
returned by the CONTAINSTABLE and FREETEXT queries between catalog repopulations in previous
versions of SQL Server.

RESOLUTION

To resolve the problem, you must upgrade the MSSearch catalog format. After
you upgrade the MSSearch catalog format, you may experience the symptoms mentioned in the
"Summary" section.

WORKAROUND

To work around the behavior you can use any one of these options:

A side-by-side upgrade
A controlled upgrade
A default upgrade

More information about each type of upgrade follows.

Side-By-Side Upgrade

Note: The full-text search catalogs will not be available until they
are rebuilt. Depending on the full-text catalog size, and available system resources, you may not have to
perform a side-by-side upgrade. If the total number of rows in all the full-text catalogs that are being
upgraded is greater than 100,000, the rebuild process may take an hour, or more, depending on available
hardware and system resources. If you have more than 100,000 rows, you may want to use a side-by-side
upgrade. Additionally, for this workaround to be complete, you must upgrade all the catalogs from all
instances of SQL Server 2000 on the computer.

To avoid significant full-text search unavailability during the rebuild process, and to minimize resource
usage following the upgrade to SQL Server 2000 SP3, follow these steps to perform a side-by-side upgrade:

Back up all your SQL Server databases. SQL Server uses these backups to create an image of your existing production computer on a development computer. For more information about creating and maintaining a development computer that is a replica of your production computer, see the "Using Standby Servers" topic in SQL Server Books Online.
If change tracking is enabled on the production server, disable the background update index and any scheduled updates. You can leave any full or incremental populations that are scheduled.
Restore the backups created in step 1 to your development computer. Make sure that the database identifiers are the same. This a requirement for successfully copying your full-text catalogs from your development computer to your production computer (see step 6).
After your backups have been applied to your development computer, pointers to your full-text catalogs will exist in your database. However, those full-text catalogs will not exist. Execute the following code for each full-text catalog so that those catalogs will be created and populated:
```
sp_fulltext_catalog 'fulltext_catalog_name', 'rebuild'
go
sp_fulltext_catalog 'fulltext_catalog_name', 'start_full'
go
```
Apply SQL Server 2000 SP3 to the development server to upgrade the full-text catalog data.
When the build process completes on all full-text catalogs on the development server, copy all the full-text catalogs to a secure location on the production server. Do not overwrite existing full-text catalog data at this time.
When possible, apply SQL Server 2000 SP3 to the production server.
After the upgrade to SQL Server 2000 SP3 completes, stop the MSSearch service.

Note: If the automatic rebuilding of your catalogs has started, stop the process for each of your catalogs by running this code:
```
sp_fulltext_catalog 'fulltext_catalog_name', 'stop'
```
When the process completes, stop the MSSearch service.
On the production server, replace the existing catalogs with the catalogs that were copied from the development server.
Start the MSSearch service.
If change tracking is enabled on the production server, when possible, perform a manual change tracking update on all full-text enabled tables by executing the following command in Query Analyzer:
```
sp_fulltext_table '%tablename%', 'update_index'
```
You do not have to rebuild any full-text catalog. Additionally, if change tracking is enabled and background index updates or scheduled updates were disabled in step 2, re-enable them.

Controlled Upgrade

To avoid significant immediate resource usage following an upgrade to SQL Server 2000 SP3, follow these steps to perform a controlled upgrade:

Note: Full-text search catalogs are not available until the rebuild process completes. A controlled upgrade minimizes resource usage immediately after the SQL Server 2000 SP3 setup installation completes; however, full-text catalogs may be unavailable for a longer period with a side-by-side upgrade.

Before you upgrade SQL Server 2000 SP3 on a production server, run this code and record the output:
```
exec sp_help_fulltext_catalogs
exec sp_help_fulltext_columns
```
The information provided by these two system stored procedures is helpful when you re-create the full-text catalogs.
Use the sp_fulltext_table and sp_fulltext_catalog stored procedures to drop all the full-text catalogs. You must drop the full-text indexed tables before you drop the full-text catalogs.
Back up your SQL Server data.
Apply SQL Server 2000 SP3. During the setup process, a warning message appears to inform you that all the full-text catalogs will be rebuilt following the upgrade to SQL Server 2000 SP3. You can ignore this message because all the full-text catalogs have been dropped.
When you want, re-create all the full-text catalogs by using the sp_fulltext_catalog stored procedure.
Add all the former full-text indexed tables and columns to their original full-text catalogs, and then start a full population on all full-text catalogs.

Default Upgrade

If the total size of all full-text catalogs is reasonably small (less than 100,000 rows in many cases), and there are ample system resources available following the SQL Server 2000 SP3 setup process, you may not have to perform a side-by-side or controlled upgrade. In those cases, you will probably experience a minimum period of downtime during the rebuild process and a brief increase in resource usage.

In all cases, before you upgrade to SQL Server 2000 SP3, test the upgrade process on a non-production server. Back up all SQL Server and full-text catalog data to make sure you have a successful migration.

MORE INFORMATION

Here is an example of the behavior you might experience.

A server has two instances of SQL Server 2000 Service Pack 2 (SP2). Each instance has four full-text catalogs. An installation of SQL Server 2000 SP3 on one instance will upgrade the catalogs on both instances. Although the catalogs on the pre-SQL Server 2000 SP3 instance have been upgraded, they will continue to work.

Note that this issue only occurs the first time any instance of SQL Server 2000 is upgraded to SQL Server 2000 SP3. Later upgrades to SQL Server 2000 SP3 on the same computer do not experience this behavior.

After the SQL Server 2000 SP3 setup successfully completes, and only when the MSSearch service starts, there is sustained moderate to heavy CPU and I/O activity. The activity is moderate to heavy because the rebuilding and repopulating of all the full-text catalogs is in progress. During this time, SQL Server cannot query the catalogs. The queries may appear to be successful, but the results returned while the full population is in progress may not be accurate. The duration of the activity and inability to be successfully queried may last no more than several minutes, but may take as long as several hours. Additionally, in the Application Log you may see error messages. For example:

When the MSSearch service starts after the SQL Server 2000 SP3 setup completes, you see an error message similar to this:

Event Type:	Information
Event Source:	Microsoft Search
Event Category:	Search Service 
Event ID:	1003
Date:		7/24/2002
Time:		5:26:51 PM
User:		N/A
Computer:	FTS8
Description:
The Search service has started.


For more information, see Help and Support Center at http://support.microsoft.com.

When MSSearch tries to mount a SQL Server full-text catalog that is the older format and it reports that the catalog is corrupted, for each full-text catalog mounted at startup by MSSearch you receive an error message similar to:

Event Type:	Information
Event Source:	MssCi
Event Category:	None
Event ID:	4138
Date:		7/24/2002
Time:		5:26:51 PM
User:		N/A
Computer:	FTS8
Description:
Content index corruption detected in component CI-RcovStorageObj1 in catalog e:\ft\SQL0000800005\Build\Indexer\NlFiles. 
Stack trace is
  0x01045027
  0x0104BE13
  0x010469E0
  0x0104D0CD
  0x01048477
  0x013BCE70
  0x013B9D9F
  0x013BA645
  0x009F4251
  0x009F450D
  0x0138CE4C
  0x0138E1D0
  0x00A32783
  0x00A2247A
  0x00A99965
  0x00AA5BDC
  0x77E8758A
				
For more information, see Help and Support Center at http://support.microsoft.com

A second error message is raised by MSSearch that indicates that the catalog that was reported corrupted is the wrong version. You receive the error message because the catalog has not yet been upgraded and rebuilt. For each catalog mounted at startup by MSSearch, you will receive one, or more, messages similar to:

Event Type:	Error
Event Source:	Microsoft Search
Event Category:	Indexer 
Event ID:	7039
Date:		7/24/2002
Time:		5:26:51 PM
User:		N/A
Computer:	FTS8
Description:
The Content Index for project <SQLServer SQL0000800005> cannot be 
loaded. Error: c0041821 - The content index data on disk is for the wrong version.  

For more information, see Help and Support Center at http://support.microsoft.com.

With MSSearch, you receive the following error message. This error message indicates that the catalog is being reset (and upgraded to the new catalog format). The reset, or rebuild, may take a couple of minutes or several hours to complete, depending on how much data must be indexed and the hardware resources available. During the rebuild process, the full-text catalog is not available.

For each catalog mounted at startup by MSSearch you receive one, or more, error messages similar to:
```
Event Type:	Information
Event Source:	Microsoft Search
Event Category:	Gatherer 
Event ID:	3041
Date:		7/24/2002
Time:		5:26:51 PM
User:		N/A
Computer:	FTS8
Description:
Project <SQLServer SQL0000800005> is being reset.

For more information, see Help and Support Center at 
http://support.microsoft.com.
```
Note: To maintain high-availability of the full-text search during an upgrade to SQL Server 2000 SP3, see the "Workaround" section.

When the rebuild process completes, for each catalog that was successfully rebuilt you receive a message similar to:

Event Type:	Information
Event Source:	Microsoft Search
Event Category:	Gatherer 
Event ID:	3018
Date:		7/24/2002
Time:		5:26:53 PM
User:		N/A
Computer:	FTS8
Description:
The end of crawl for project <SQLServer SQL0000800005> has been detected. The Gatherer 
successfully processed 0 documents totaling 0K. It failed to filter 0 documents. 0 URLs could 
not be reached or were denied access.

For more information, see Help and Support Center at 
http://support.microsoft.com.

If any errors occur during the rebuild process, you may have to drop, and then re-create the affected full-text catalogs.