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

by

Troubleshooting Guide: Error 9970 – Full-Text Key Offline

Why This Error Happens

Error 9970 occurs when the full-text key column (the unique index column used by the Full-Text Engine) for a table or indexed view is marked offline, typically because the underlying filegroup containing the full-text key index is unavailable or has been taken offline. This can also happen when the database is in a suspect or recovery-pending state, preventing SQL Server 2025 from validating the uniqueness of the key during full-text crawl or query operations. Corrupted full-text catalog metadata or an incomplete database restore/attach operation can also trigger this condition.

Step-by-Step Resolution Guide

Step 1: Verify the Filegroup and Database State

  • Open SQL Server Management Studio (SSMS) and connect to the SQL Server 2025 instance.
  • Run the following query to check filegroup status:
SELECT name, state_desc FROM sys.filegroups;
  • Confirm the database itself is online by running:
SELECT name, state_desc FROM sys.databases WHERE name = 'YourDatabaseName';
  • If the state shows “OFFLINE” or “RECOVERY_PENDING,” proceed to bring the filegroup/database back online.

Step 2: Bring the Offline Filegroup or Database Online

  • If a specific filegroup is offline, run:
ALTER DATABASE YourDatabaseName MODIFY FILEGROUP [FilegroupName] ONLINE;
  • If the entire database is offline due to a file issue, locate the physical data file path (commonly under C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\ on Windows Server 2025).
  • Ensure the SQL Server service account has Read/Write NTFS permissions on this folder via File Properties > Security > Edit.
  • Attempt to bring the database online:
ALTER DATABASE YourDatabaseName SET ONLINE;

Step 3: Rebuild the Full-Text Index

  • Once the filegroup is online, the full-text key index itself may need repair. Drop and recreate the full-text index on the affected table:
DROP FULLTEXT INDEX ON YourTableName;
GO
CREATE FULLTEXT INDEX ON YourTableName(ColumnName)
KEY INDEX PK_YourTableName
ON YourFullTextCatalogName;
GO
  • Start a full repopulation to ensure the crawl completes cleanly:
ALTER FULLTEXT INDEX ON YourTableName START FULL POPULATION;

Step 4: Restart the Full-Text Daemon Service

  • Open Services.msc on Windows Server 2025 / Windows 11.
  • Locate SQL Full-text Filter Daemon Launcher (MSSQLSERVER).
  • Right-click and select Restart to clear any stale locks on the full-text key index.

Alternative or Verification Steps

  • Verify the fix by querying sys.fulltext_indexes and checking that the is_enabled column returns 1, and confirm the crawl status via SELECT FULLTEXTCATALOGPROPERTY('CatalogName', 'PopulateStatus'); (a result of 0 indicates idle/success).
  • If the filegroup remains corrupted and cannot be brought online, restore the database from the last known good backup using RESTORE DATABASE YourDatabaseName FROM DISK = 'BackupPath' WITH RECOVERY; to reset the full-text key indexes entirely.