Let me comment on a few problems you saw so that anyone who gets into your situation can benefit of reading this post.
1. CPU usage goes to 100% after upgrade(whether it is attach, restore, or in-place upgrade) due to full-text indexing.
The idea behind this behavior is that we want to utilize as much resource as possible to finish rebuild full-text indexing so it can be back online quickly. It is aimed for the application which heavily rely on full-text functionality(those users quite often have large number of full-text catalogs and large full-text catalog in their databases. They are more likely want to use 100% CPU.). From your experience, it sounds like we made a bad decision about the default behavior. Maybe it is better to let server rebuilding index slower by default and for advanced full-text user let them tune the rebuild performance. Once thing you can do to alleviate your situation: sp_fulltext_service ‘pause_indexing’, 0 will pause all full-text index on the box until you resume it or restart sqlserver service. Even though we use 100% CPU, but we should never used up all memory on the server during the rebuild. The page fault you saw is probably due to the search service use OS file mapping to read and write index files. Those page faults are due to I/O operation.
2. Upon further review of our logs, we noticed this: "Warning: No appropriate filter was found during full-text index population for table or indexed view '[MyDB].[dbo].[MyTable]' (table or indexed view ID 'X', database ID '1784393426'), full-text key value 0x0001E119. Some columns of the row were not indexed" So, it appeared that we had some sort of corruption with the full-text index, and the problem likely to be with the Filter that is not available for FTS.
The failure is due to third party filter installed on OS will not be loaded by default due to security concern. Your table must have used some file format which is not supported out of box in SQL 2000. To fix this problem, you will need to turn on sp_fulltext_service ‘load_os_resources’, 1. If the third party filter is not signed, you will also need to turn off signature verification in order to use it. Use sp_fulltext_service ‘signature_verification’, 0 to turn it off.
3. Backup failed due to full-text offline.
Full-text catalog modeled as a database file in SQL 2005, that’s how user get integrated manageability improvement. User can backup/restore individual full-text catalog, detach/attach database with full-text catalog, include full-text catalog in db mirroring setup, etc. This also post a new restriction to the user that if full-text catalog is offline you cannot backup the whole database(This is same as if a database file is offline, you cannot take full database backup.) . While you restoring SQL 2000 backup, since full-text catalog never is part of SQL 2000 backup. You have no way to relocate the full-text catalog. So after restore, it is very important to check sys.master_files to see if all your files are online. For full-text catalog, if the catalog path point to a invalid path, it will be offline. You can use alter database modify file to correct the path and rebuild the catalog to bring it online. Or simply drop the catalog. In SQL 2005 SP2, we have improvement error reporting for this kind of backup failure so user can fix or drop their full-text catalog before full database backup.
4. Full-text catalog 'AdiosMycatalog' has been lost. Use sp_fulltext_catalog to rebuild and to repopulate this full-text catalog" Lost!?
I would like to know more detail about this problem. First, we don’t have “Lost” error message. Please give me the real error message so I can see if there is a bug. Second, a full-text catalog or index can be drop when database is not full-text enabled, full-text is not installed, or full-text catalog is offline. The fact you can drop all fulltext catalogs by using detach without keep fulltext catalogs tells me that drop should go through.
Contact me if you have more issues. I will be happy to help you resolve them.
Thanks,
Jingwei Lu
Microsoft SQL Server