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

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.

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.

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:

1. 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.
2. 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.
3. 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).
4. 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

5. Apply SQL Server 2000 SP3 to the development server to upgrade the full-text catalog data.
6. 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.
7. When possible, apply SQL Server 2000 SP3 to the production server.
8. 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.
9. On the production server, replace the existing catalogs with the catalogs that were copied from the development server.
10. Start the MSSearch service.
11. 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.

1. 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.
2. 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.
3. Back up your SQL Server data.
4. 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.
5. When you want, re-create all the full-text catalogs by using the sp_fulltext_catalog stored procedure.
6. 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.

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.