The following story you are about to read is true. In no

way is the attempted humor meant to mask the seriousness of the issue, nor

harm, insult or embarrass anyone, except perhaps myself. I write this article to share my experiences and give you a heads up as to what you might encounter during a database migration from SQL 2000.

One of the most common ways of migrating our databases

from SQL Server 2000 to 2005, is the old trusted method of backup and

restore. SQL Server 2005 allows us to restore a 2000 database, and gives us the option of bringing the compatibility level up to 9.0 (2005).Therefore, it is perhaps one of the quickest, simple, and reliable ways of migrating the database, ensuring that all data, objects, and users come over.

I know that I have migrated many SQL Server databases in this manner.However, until recently, I ran into some full-text search (FTS) issues with a particular database where a full-text index and catalog was created on the database, but never really utilized in production. Nonetheless, several entries with respect to this Full-Text definition were made into the system tables, and caused some major headaches.

What happened next ranges from the ridiculous to the sublime.Either occurrence I am about to describe is unacceptable, and certainly would be considered a show-stopper to deploying your SQL2K5 instance into production. First, take notice of the fact that in SQL 2005, FTS is installed as a separate service/component. Depending on how you specify the start-up option during the install, usually it will show up in the control panel as 'Automatic' startup.

Quick background on our server resources. We're running SQL Server 2005 64-bit Standard Edition SP1; Windows Server 2003 R2 x64 Enterprise Edition, 4 Dual-Core Processors (with hyper-threading that's 16 logical CPUs), with 12 GB of purely addressable RAM. One powerful box!

In our environment, when we first attempted to restore the 2000 database to our new 2005 server, we watched the restore complete successfully, and then the full-text engine kicked in attempting to rebuild the catalog on the database.Here we watched helplessly, as if witnessing a tsunami approaching but unable to get out of its way. We monitored the CPU usage and saw it climb incrementally from 0 to 100% flat-lining across all processors, maxing out memory and page-faulting all over itself. At that point, crucial migration on the line, we checked our pulses as well!

Fortunately, breathing resumed and we were ok, but no such luck for the server.It was hung, and any attempt to regain control was unsuccessful, leaving us no choice but to reboot the machine.By the way, we did try one of SQL 2005's newest features, the dedicated administrator's connection DAC "BOL: a special diagnostic connection for administrators when standard connections to the server are not possible. It allows an administrator to access SQL Server to execute diagnostic queries and troubleshoot problems even when SQL Server is not responding to standard connection requests." (In our case, Do not Attempt Control :-). However, please do remember to enable the DAC via Surface Area Configuration for Features, as it might come in handy since it's not enabled by default oops!

The good thing about SQL Server 2005 is that with the help of its very extensive and verbose error logging, we very quickly realized where the problem was, and identified that Full-Text Search was indeed the causus-belli. We even were able to reproduce the behavior at-will.I'd say we're now resident experts on how to crash the mightiest of servers. One message appearing in the SQL FT log clued us in, "The full-text catalog monitor reported catalog "MyCatalog" in database "MyDB" in REINITIALIZE state. This is an informational message only. No user action is required." No kidding! All joking aside, all the above was useful for our support call with Microsoft.

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.

Once we finally got the situation under control, where we would disable the FTS service temporarily while working to resolve issue, we proceeded to create a simple backup plan to regularly backup our newly migrated 2005 database. To our horror, when the backup kicked off it failed straight away, and we receive the error:

"Error message when you perform a full backup of a database in SQL Server 2005: Backup failed for Server 'ComputerName\SQLInstanceName' or The backup of the file or filegroup 'sysft_FullTextCatalog' is not permitted because it is not online"

So, now this was personal.How can we deploy this database and application in production when we can't even take a backup?!Fortunately, the aforementioned error appears to be a known issue at Redmond, and has a KB-article on same: http://support.microsoft.com/kb/923355/en-us.

According to the article, "This behavior occurs because a full-text catalog in the database is not online. To perform a full backup, SQL Server 2005 requires all the database files and full-text catalogs in the database to be online." Great!So, in order to be able to backup the database, our choices were 1) enable FTS, and therefore crash the

server, or 2) hey, who needs a backup anyway?

Although the article is gives various potential causes and resolutions, it doesn't seem to clearly indicate what commands, and in what order, to execute to get around the issue.

For example, the KB-article suggests, "If you do not need the full-text catalog any longer, you can drop the problematic full-text catalog. Then, perform a full backup of the database in SQL Server 2005." Ok, in our case we no longer needed the catalog. Whew! That sounds simple enough.So I proceed with the system stored procedure:sp_fulltext_catalog 'AdiosMyCatalog', 'drop'.Result: "Full-text catalog 'AdiosMycatalog' has been lost. Use sp_fulltext_catalog to rebuild and to repopulate this full-text catalog" Lost!? I don't remember losing it anywhere. I looked around for it, but no luck.

Back to the KB-article for more clues:

Sounds plausible.Full-text is not enabled on the database, and it was restored from a MS SQL 2000 backup. Must be getting warmer. I figured I'd do as the error msg says (not as the error msg does), and attempted to rebuild the catalog (thinking it would at least refresh any values it needed, to then be able to successfully drop the catalog.) Upon query execution, I get my confirmation:

"Full-Text Search is not enabled for the current database. Use sp_fulltext_database to enable Full-Text search." A no-brainer. Use MyDB, sp_fulltext_database 'enable' slam dunkwhat the !@#$%? "Full-text catalog 'AdiosMycatalog' has been lost" Again?? Seems now that I was lost too.

After some research on the 'Net searching for fellow survivors of "Lost" no, not the TV show (http://www.tv.com/lost/show/24313/summary.html), I came across a potential solution in the MSDN forums. Suggested was the following:

Look at the sysfulltextcatalogs table in your database:

SELECT * FROM sysfulltextcatalogs

If there is an entry, get the Ftcatid and look your sysobjects:

SELECT * FROM sysobjects 
 WHERE ftcatid IN (SELECT ftcatid FROM sysfulltextcatalogs)

OR

SELECT * FROM sysobjects WHERE ftcatid > 0

If you encounter the objects, update the sysobjects table:

UPDATE sysobjects SET ftcatid = 0 
 WHERE ftcatid > 0 
  or IN (SELECT ftcatid FROM sysfulltextcatalogs)

Well, I had nothing to lose, since I already lost my catalog ok, enough with the "lost" jokes. I certainly won't forget the wisdom of the poster who warns us in red letter italics (I kid you not): "But, remember

Backup Full before [running] these procedures"I took my chances. After executing the above statements, I proceeded to run the below system stored procs, in the following order:

sp_fulltext_table 'SoLongMyTable', 'drop'
go
sp_fulltext_database 'enable'
go
sp_fulltext_catalog 'HastaLaVistaMyCatalog', 'drop'

go
sp_fulltext_service 'clean_up'
go

And, alas, no error messages! All was not lost. 😛 Definitely, a positive

sign, but now for the ultimate test the database backup. Right-click >> Tasks >>

Backup.After several minutes, I was satisfied that indeed the issue has been

resolved, and I had my first good backup of the SQL Server 2005 database.

Now, although the above full-text SQL

statements are valid in SQL 2005, here is a list of those that are deprecated

and its replacements.Keep in mind that

they may be removed altogether in future versions of SQL Server.

It might be worthy to note that upgrading to SQL Server

2005 from a previous SQL Server version is supported by the SQL Server 2005

Setup program. You can also migrate databases from previous SQL Server versions

to an instance of SQL Server 2005.If

you have full-text indexes present, the upgrade process marks your databases as

full-text disabled, and hence catalogs must be repopulated. Since this

operation can be time and resource consuming, it is not run automatically by

the setup.Therefore, it is quite

conceivable that because we chose to migrate our database via backup/restore,

we did not have the full-text properly disabled. So plan carefully and consider what is the

best upgrade method based on your current database environment.

There is one more solution that was suggested by

another poster, which has yielded positive results. Make sure you're MSSQL 2005 instance is on

SP1, then right-click, tasks, and select 'Detach' to detach the database. Uncheck the option "Keep Full Text

Catalogs". Then, reattach the database by right-clicking, tasks, 'Attach', and

search to the path of the data files.  The backup now completes

successfully!

Many thanks to those folks out there who posted their similar issues and resolutions it was indeed a great help.Subsequently, I requested a full-refund from Microsoft support, for being a known issue, yet unable to get to a quick resolution.I'm still waiting to hear back from them, I guess their server is hung indefinitely.

Written by: Robert Pearl, President

Pearl Knowledge Solutions, Inc.

rsp05@pearlknows.com

http://www.pearlknows.com

Copyright 2007 - All Rights Reserved.

Note: Not to be reprinted or published without express permission of the

author.

When SQL is the Center of Your Universe!