Removing a Full Text Catalog

• Robert Walker

  SSC Journeyman

  Points: 98

  More actions

  May 3, 2005 at 8:46 am

  #164419

  I am trying to shrink a 50 Gb Db and I believe the Full Text Catalog is getting in the way. But trying to delete this object has not proven easy. I have tried the following:

  EXEC sp_fulltext_database 'enable'

  EXEC sp_fulltext_catalog 'ux_t100_full_text', 'drop'

  But the files are already gone so this returns an error

  Access is denied or the path is invalid. Full-text search was not installed properly.

  I admit I do not have allot of experience with full text indexes. Any help would be greatly appreciated.

   

  Thanks

  Robert W.

• John Kane

  SSCrazy

  Points: 2810

  More actions

  May 4, 2005 at 12:56 am

  #556668

  Robert,

  First of all, removing the Full Text Catalog will not shrink the size of your 50 Gb Db because the FT Catalog folder and files are maintained outside of the database. Depending upon what portion of the database or log of the database you want to shrink, you should use DBCC SHRINKDATABASE or DBCC SHRINKFILE, see the following two links for more info:

  DBCC SHRINKDATABASE - Shrinks the size of the data files in the specified database.

  http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_3pd1.asp

  INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE

  http://support.microsoft.com/default.aspx?scid=kb;en-us;272318

  If the external files are are gone or deleted, then this is not an error, as you cannot drop or delete an already deleted FT Catalog and this *error* is to be expected. You should review the above two links for shrinking the database and/or the log of the database.

  Regards,

  John

  SQL Full Text Search Blog

  http://spaces.msn.com/members/jtkane/

   

  
  John T. Kane

• Robert Walker

  SSC Journeyman

  Points: 98

  More actions

  May 4, 2005 at 7:28 am

  #556755

  Thanks, that makes sence and works great! But why is the catalog sill showing in Enterprise Manager under Full-Text Catalogs?

  Thanks

  rw

   

• John Kane

  SSCrazy

  Points: 2810

  More actions

  May 4, 2005 at 11:48 am

  #556867

  You're welcome, Robert,

  There may be entries in the system table sysfulltextcatalogs for the deleted FT Catalog &/or there may be registry keys for the FT Catalog folder name (e.g., SQL00050005 for example) that may need to be manually deleted. You may be able to use the following TSQL code:

  use <user_database>

  EXEC sp_fulltext_service 'clean_up'

  as this should Searches for and removes the full-text catalog resources in the file system that do not have corresponding entries in sysfulltextcatalogs. If the above fails to clean-up the deleted entries, you may want to manually delete the entry out of fulltextcatalogs via:

  -- Enable System Table UPDATEs

  sp_configure allow,1

  go

  reconfigure with override

  go

  use <user_database>

  go

  -- Record FT Catalog info. (Note: path = NULL)

  select * from sysfulltextcatalogs

  go

  UPDATE sysfulltextcatalogs set path = '<new_drive_letter:\<new_directory>'

      WHERE ftcatid = <ftcatid_of_affected_catalog>  -- or don't use WHERE

  clause,

  go

  -- Record new FT Catalog info.

  select * from sysfulltextcatalogs

  go

  use master

  go

  -- Disenable System Table UPDATEs

  sp_configure allow,0

  go

  reconfigure with override

  go

  Hope this helps!

  John

  SQL Full Text Search Blog

  http://spaces.msn.com/members/jtkane/

  
  John T. Kane

• Robert Walker

  SSC Journeyman

  Points: 98

  More actions

  May 4, 2005 at 12:35 pm

  #556885

  Thanks again...it was an entry in the sys table

  rw