Freigeben über

FAILED TO DROP Full TEXT Catalog ON A DATABASE after upgrading to sql server 2005

  • Artikel

Consider the following scenario where you upgraded a database from sql server 2000 to sql server 2005 instance and changed the compatibility of the database to 90 and now you wish to drop the Full Text catalog which was originally created in the sql server 2000 database and is still present in the upgraded sql server 2005 database.

While trying to drop the Full text catalog in the upgraded sql server 2005 database you may receive the following error message

 DROP FULLTEXT CATALOG <catalog_name>

ERROR MESSAGE:
====================
Drop failed for FullTextCatalog 'ix_Teamx_NEW_SITE'. (Microsoft.SqlServer.Smo)

For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+FullTextCatalog&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.
A severe error occurred on the current command. The results, if any, should be discarded.
Location: fulltext.cpp:2369
Expression: m_DropLSN == NullLSN
SPID: 57
Process ID: 1424 (Microsoft SQL Server, Error: 3624)

The above error can be misleading and as it indicates that there is corruption in the database which is not the case.

CAUSE
=====

The issue is caused since the Full text catalog was created by the sql server 2000 instance and could not be deleted by the sql server 2005 engine.

Resolution
=======

In order to resolve the issue we should detach the database back to the sql server 2000 instance, drop the full text catalog in question and attach the database back to the sql server 2005 instance.

As a Good practice you should drop the Full Text Catalog while dettaching the database from sql server 2000 instance while upgrading or migrating to the sql server 2005 and recreate it in sql server 2005 instance.

Note:

This post should not be treated as the Microsoft’s Recommendation or Resolution to the problem, it is only a workaround which worked in our environment and so we would like to share it.

Parikshit Savjani
SE, Microsoft SQL Server

Comments

  • Anonymous
    February 18, 2009
    PingBack from http://www.clickandsolve.com/?p=10984

  • Anonymous
    June 09, 2009
    Can you elaborate on this: "we should detach the database back to the sql server 2000 instance" It sounds like you're saying the user should detach the database from their SQL Server 2005 instance and reattach it to their SQL Server 2000 instance.  I didn't think you could detach a database from a 2005 instance and reattach it to a 2000 instance. Can you explain in more detail what you're recommending?  Thanks!

  • Anonymous
    June 09, 2009
    Hi Brent, You are absoutely right, SQL 2005 database cannot be attached back to sql 2000 server. In this post, I meant to attach the same mdf & ldf files which were taken from sql 2000 server back to sql 2000 server and delete the Full text catalog. In a nutshell, the idea is backup from sql 2000 or files should restored back to sql 2000 and Full text catalog should be deleted before attaching it to sql 2005. My bad on the ambigious stattement, thanks for poiting that out Parikshit

  • Anonymous
    July 05, 2009
    The solution given is basically 'start again'. Is there any solution to this problem assuming you only have the database in sql 2005, ie the 2000 instance no longer exists? thanks.

  • Anonymous
    July 06, 2009
    Hi Rory, There is no supported method for dropping the Full Text Catalog besides the once discussed in above article. If you have such scenario, you can get in touch with CSS. Parikshit

  • Anonymous
    November 09, 2009
    Hi, I have a similar problem. Since a approximately two weeks the backup of a database fails because the fulltext catalog cannot be backed up. (SQL Server says it cannot backup the fulltext catalog because it is not online.) Both the database and its fulltext catalog worked fine for approximately one year. It never was hosted on any other SQL Server version than 2005. So I tried to recreate the fulltext catalog, but this action loops indefinitely. Next try was to drop the fulltext catalog which resulted in the crash of fulltext.cpp. Is there any option I could make my databsse work fully again? Best regards, Dietmar.

  • Anonymous
    November 09, 2009
    I am not sure whether this issue occurs when sql server is hosted on the same version throughout.. If that is the issue you may want to open a case with CSS and investigate what can cause it Regards Parikshit

  • Anonymous
    January 19, 2010
    I got this error when backing up a 2005 database restored from a 2000 instance. I performed the following steps to drop the full-text catalog:

  1. Enable the (target) database for full-text indexing.
  2. Run script generated by "Script Catalog as... DROP to " to drop the full-text catalog.
  • Anonymous
    March 27, 2010
    Hello I had this problem when I migrated from SQL2000 to SQL2005 express, and got this error message when I tried to remove the Fulltext Catalog: Cannot drop full-text catalog 'ix_whatever' because it contains a full-text index. Then I searched the web and here is the solution to the problem. Follow these three simple steps to remove the fulltext catalog.:
  1. In the database that uses the full text catalog run: SELECT name, ftcatid FROM sysobjects WHERE ftcatid > 0
  2. That will give you a list of the tables which use it, then you can mark each table not to use full text: EXEC sp_fulltext_table 'tbl_whichtable', 'drop'
  3. Then eventually again try: DROP catalog 'ix_whatever'
  • Anonymous
    March 29, 2010
    Hi Kristian, The method you mentioned worked for you, but it appears that the error you received is different from the one this post was written for (an assertion following by a mini-dump when running the DROP command) Basically, you're dropping the table (and columns) from full-text indexing. Also using sp_fulltext_table while it will work, is risky as that will be removed in a future release (it's a deprecated command). This was the reason, that with SQL 2005, DML support was introduced for full-text search. HTH. Sudarshan