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

by

Why This Error Happens

Error 3986 occurs when the internal global transaction timestamp counter used by SQL Server’s In-Memory OLTP (Hekaton) engine reaches its maximum allowable value. This counter is a lightweight, monotonically increasing value used to order transactions across memory-optimized tables, and once exhausted, SQL Server cannot generate new timestamps for incoming transactions. This typically happens on servers with extremely high transaction volumes against memory-optimized objects that have been running for a very long time without a restart, or due to a bug in older builds that increments the counter more aggressively than expected.

Step-by-Step Resolution Guide

Step 1: Confirm the Scope of the Issue

  • Check the SQL Server Error Log (Management > SQL Server Logs in SSMS, or C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for repeated instances of Error 3986.
  • Identify whether the affected database(s) use memory-optimized tables (In-Memory OLTP) by running:
SELECT name, is_memory_optimized_elevate_to_snapshot_on 
FROM sys.databases;
  • Note the exact time of failure to correlate with transaction volume spikes.

Step 2: Perform a Controlled Restart of the SQL Server Service

  • Open Services.msc on Windows Server 2025 / Windows 11 and locate SQL Server (MSSQLSERVER) or the named instance.
  • Right-click and select Restart, or run from an elevated PowerShell prompt:
Restart-Service -Name 'MSSQLSERVER' -Force
  • This resets the in-memory transaction timestamp counter, which is the only way to recover from this specific error once it occurs.

Step 3: Apply the Latest Cumulative Update (CU) for SQL Server 2025

  • Navigate to the Microsoft Update Catalog and download the latest Cumulative Update for SQL Server 2025.
  • Install the patch using the setup wizard or silently via:
Setup.exe /Action=Patch /IAcceptSQLServerLicenseTerms /Quiet /InstanceName=MSSQLSERVER
  • This CU includes fixes to the timestamp allocation logic that reduce the frequency of counter exhaustion under heavy In-Memory OLTP workloads.

Step 4: Schedule Proactive Maintenance Restarts

  • Configure a maintenance plan or Task Scheduler job to restart the SQL Server service during a low-traffic window (e.g., weekly) to preemptively reset the timestamp counter.
  • Use SQL Server Agent to create a job with a CmdExec step:
net stop MSSQLSERVER && net start MSSQLSERVER
  • Document this as a known mitigation until the underlying workload is optimized or reduced.

Alternative or Verification Steps

  • After restarting, verify the fix by monitoring the Error Log for 24–48 hours to confirm Error 3986 does not recur, and check sys.dm_db_xtp_transaction_stats for healthy timestamp generation.
  • If the error reoccurs frequently, consider reviewing application logic to reduce unnecessary transaction churn on memory-optimized tables, or open a support case with Microsoft to evaluate a hotfix specific to your build number.