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

by

Troubleshooting Guide: Error 9979 – Full-Text Catalog Offline After Upgrade

Why This Error Happens

This error occurs during a SQL Server upgrade when the engine attempts to recreate existing full-text catalogs at their original file system path, but fails to do so. The most common root causes are the original catalog directory no longer existing, the SQL Server service account lacking NTFS write permissions on the target path, or the path referencing a drive letter/mount point that is invalid on the new Windows Server 2025 or Windows 11 host. Because the catalog cannot be physically created, SQL Server marks it as OFFLINE rather than failing the entire upgrade.

Step-by-Step Resolution Guide

Step 1: Identify the Failed Catalog and Original Path

  • Check the SQL Server Error Log or upgrade log (located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for the exact catalog name and path referenced in Error 9979.
  • Run the following query to confirm the catalog status:
SELECT name, is_fulltext_enabled, data_space_id 
FROM sys.fulltext_catalogs;
  • Note the database name and catalog name reported in the warning message.

Step 2: Verify and Correct File System Permissions

  • Navigate to the intended catalog path (e.g., D:\SQLData\FTCatalogs\) in File Explorer.
  • Right-click the folder, select Properties > Security, and confirm the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Full Control.
  • If the folder does not exist, create it manually and grant permissions using PowerShell:
icacls "D:\SQLData\FTCatalogs" /grant "NT SERVICE\MSSQLSERVER:(OI)(CI)F"

Step 3: Rebuild the Offline Full-Text Catalog

  • Connect to the affected database in SQL Server Management Studio (SSMS).
  • Run the rebuild command, pointing to a valid, accessible path:
ALTER FULLTEXT CATALOG [YourCatalogName] REBUILD;
  • If the original path is permanently unavailable, drop and recreate the catalog with a new path:
DROP FULLTEXT CATALOG [YourCatalogName];
CREATE FULLTEXT CATALOG [YourCatalogName] 
IN PATH 'D:\SQLData\FTCatalogs' 
AS DEFAULT;

Step 4: Re-associate Full-Text Indexes

  • If the catalog was dropped and recreated, re-link any full-text indexes that were previously pointing to it.
  • Use ALTER FULLTEXT INDEX ON [TableName] SET CHANGE_TRACKING AUTO; to restore automatic population and ensure indexing resumes immediately.

Alternative or Verification Steps

  • Verify the Fix: Run SELECT name, is_fulltext_enabled FROM sys.fulltext_catalogs WHERE name = 'YourCatalogName'; and confirm the catalog status in the SSMS Object Explorer shows “Online” rather than “Offline/Unavailable.”
  • Alternative Solution: If permissions continue to block creation, temporarily move the catalog to the default SQL Server data directory (e.g., C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\), which inherently has the correct service account permissions already configured.