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

by

Troubleshooting Guide: Error 3947 – Secondary Compute Failed to Catch Up Redo

Why This Error Happens

This error occurs in SQL Server 2025 configurations utilizing compute replicas (such as Hyperscale or Always On secondary compute nodes) where the redo thread on the secondary falls too far behind the primary’s log generation rate. This lag is typically caused by insufficient I/O throughput, CPU bottlenecks on the secondary compute node, or network latency between the primary and secondary replicas that prevents timely log shipping. When the redo queue exceeds the internal threshold, SQL Server proactively aborts the transaction to prevent unbounded data divergence rather than allowing the secondary to fall further out of sync.

Step-by-Step Resolution Guide

Step 1: Verify Secondary Compute Resource Health

  • Open Performance Monitor (PerfMon) on the secondary node and check the SQL Server:Database Replica > Redo Queue Size and Recovery Queue counters.
  • Confirm CPU and Disk Queue Length are not consistently maxed out using Task Manager or Resource Monitor.
  • Run the following query on the secondary to check redo lag in real time:
  SELECT database_id, redo_queue_size, redo_rate, last_redo_time 
  FROM sys.dm_hadr_database_replica_states;

Step 2: Increase Redo Throughput Capacity

  • Navigate to SQL Server Configuration Manager > SQL Server Services and confirm the secondary instance has parallel redo enabled (default in SQL Server 2025, but verify via trace flag status).
  • If running on constrained hardware, increase vCPU allocation for the secondary compute node in your Windows Server 2025 Hyper-V or Azure VM configuration settings.
  • Check HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters for any legacy startup parameters limiting worker threads, and remove unnecessary restrictions.

Step 3: Reduce Network Latency Between Replicas

  • Use Test-NetConnection -ComputerName <PrimaryNodeName> -Port 5022 in PowerShell on the secondary to validate the Always On endpoint connectivity and latency.
  • Ensure both primary and secondary nodes reside in the same Availability Zone or low-latency subnet if hosted in a virtualized or cloud environment.
  • Adjust the SESSION_TIMEOUT property on the Availability Group endpoint if network jitter is causing premature session drops:
  ALTER AVAILABILITY GROUP [YourAGName] 
  MODIFY REPLICA ON 'SecondaryNodeName' 
  WITH (SESSION_TIMEOUT = 30);

Step 4: Retry the Transaction and Monitor the Log

  • Manually retry the failed transaction once resource contention has cleared.
  • Check the SQL Server Error Log (located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for recurring 3947 events to confirm the issue is resolved rather than intermittent.

Alternative or Verification Steps

  • Verify the Fix: Re-run the sys.dm_hadr_database_replica_states query from Step 1; the redo_queue_size should trend toward zero and remain stable under load.
  • Alternative Solution: If hardware constraints persist, consider scaling out the secondary compute tier (e.g., upgrading the Hyperscale compute size) or temporarily removing the lagging replica from synchronous commit mode to asynchronous to reduce pressure during peak loads.