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

  • Article

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