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

by

Troubleshooting Guide: SQL Server Error 9985 – Insufficient Memory for Search Property List Cache

Why This Error Happens

Error 9985 occurs when SQL Server 2025’s Full-Text Engine cannot allocate enough memory from the buffer pool to build or load the search property list cache during an indexing operation. This is typically caused by memory pressure from other SQL Server processes, an undersized “max server memory” configuration, or a large number of concurrent full-text indexing requests competing for the same resource pool. In some cases, a corrupted or bloated search property list in the sys.registered_search_property_lists catalog can also inflate the memory footprint required to load it.

Step-by-Step Resolution Guide

Step 1: Check and Adjust SQL Server Memory Configuration

  • Open SQL Server Management Studio (SSMS) and connect to the instance.
  • Run EXEC sp_configure 'max server memory (MB)'; to check the current ceiling.
  • If the value is too restrictive relative to available system RAM, increase it:
  EXEC sp_configure 'show advanced options', 1;
  RECONFIGURE;
  EXEC sp_configure 'max server memory (MB)', 16384; -- adjust based on server RAM
  RECONFIGURE;
  • On Windows Server 2025/Windows 11, confirm total physical RAM via Task Manager > Performance tab, and leave at least 20-25% headroom for the OS.

Step 2: Reduce Full-Text Indexing Concurrency

  • Open a query window and check active full-text crawls:
  SELECT * FROM sys.dm_fts_active_catalogs;
  • Pause or stagger full-text population jobs instead of running them simultaneously:
  ALTER FULLTEXT INDEX ON [YourTable] STOP POPULATION;
  • Reschedule large catalog rebuilds during off-peak hours using SQL Server Agent jobs.

Step 3: Rebuild or Clean Up the Search Property List

  • Identify the affected property list:
  SELECT * FROM sys.registered_search_property_lists;
  • If the list is excessively large or suspected corrupted, drop and recreate it:
  DROP SEARCH PROPERTY LIST [YourPropertyListName];
  CREATE SEARCH PROPERTY LIST [YourPropertyListName];
  • Re-associate the new property list with the relevant full-text index via SSMS (Full-Text Index Properties > Property List).

Step 4: Clear SQL Server Internal Caches

  • Flush unused memory caches to relieve pressure before retrying the indexing statement:
  DBCC FREESYSTEMCACHE('ALL');
  DBCC DROPCLEANBUFFERS; -- use only in non-production or maintenance windows
  • Restart the SQL Server Full-Text Filter Daemon Launcher service via services.msc (service name: MSSQLFDLauncher).

Step 5: Validate Windows Server Resource Allocation

  • Open Task Manager or Resource Monitor to confirm no other process (e.g., antivirus, backup agents) is consuming excessive RAM during the indexing window.
  • Check Windows Server 2025 memory compression and paging file settings under System Properties > Advanced > Performance Settings, ensuring the page file isn’t undersized relative to physical RAM.

Alternative or Verification Steps

  • After applying the changes, rerun the original full-text indexing statement and confirm Error 9985 no longer appears in the SQL Server Error Log (EXEC xp_readerrorlog 0, 1, N'9985';).
  • If the error persists, as a workaround, temporarily reduce the number of columns/properties in the search property list or split large full-text catalogs into smaller, separately populated catalogs to lower peak memory demand.