How to fix Error 9947 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: Error 9947 – Full-Text Catalog Identity Mismatch

Why This Error Happens

Error 9947 occurs when SQL Server 2025 attempts to attach a database and detects that the full-text catalog files on disk carry an internal identity (GUID) that does not match the identity recorded in the database metadata. This typically happens when a database is restored or copied from another instance without properly moving the associated full-text catalog folder, or when the catalog files become orphaned due to a mismatched attach/detach operation. Corruption of the catalog’s metadata file, manual renaming of catalog directories, or attaching a database to a different SQL Server instance than the one that originally created the catalog can also trigger this mismatch.

Step-by-Step Resolution Guide

Step 1: Detach the Database Cleanly

  • Open SQL Server Management Studio (SSMS) and connect to your Windows Server 2025 instance.
  • Run EXEC sp_detach_db 'YourDatabaseName'; to safely detach the database before making file changes.
  • Ensure no active connections are using the database by running ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE; beforehand.

Step 2: Verify and Relocate the Full-Text Catalog Files

  • Navigate to the default data directory, typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\.
  • Confirm the full-text catalog folder (often named with a GUID, e.g., FTData\sql_ft_catalog_<GUID>) exists and matches the path referenced during attach.
  • If the folder was moved or renamed, restore it to its original path or update the attach script to reference the correct location.

Step 3: Re-attach the Database Without the Corrupted Catalog

  • Use CREATE DATABASE YourDatabaseName ON (FILENAME = 'C:\...\YourDatabaseName.mdf') FOR ATTACH; while excluding the full-text catalog file reference if it continues to fail.
  • If the attach succeeds without full-text catalog, proceed to rebuild it manually in Step 4.

Step 4: Rebuild the Full-Text Catalog from Scratch

  • Drop the broken catalog reference: DROP FULLTEXT CATALOG ft_catalog_name; (run this against the newly attached database).
  • Recreate it using CREATE FULLTEXT CATALOG ft_catalog_name AS DEFAULT;
  • Re-associate the full-text index: CREATE FULLTEXT INDEX ON YourTable(YourColumn) KEY INDEX PK_YourTable ON ft_catalog_name;

Step 5: Run Full Population to Restore Indexing

  • Execute ALTER FULLTEXT INDEX ON YourTable START FULL POPULATION; to rebuild the index data.
  • Monitor progress via SELECT * FROM sys.dm_fts_index_population; until the population status shows as idle (completed).

Alternative or Verification Steps

  • Verify the fix by querying SELECT * FROM sys.fulltext_catalogs; and confirming the catalog is listed as “ONLINE” with no error flags.
  • If rebuilding fails repeatedly, restore the database from a known-good backup that includes a consistent full-text catalog, rather than attempting a manual attach of mismatched files.