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

by

Troubleshooting Guide: SQL Server Error 3948 in Always On Availability Groups

Why This Error Happens

Error 3948 occurs when a long-running transaction under snapshot isolation (or RCSI) on a secondary replica requires row versions that have already been removed by the ghost cleanup process on the primary replica. This typically happens because the secondary replica fell behind in redo, the availability replica underwent a role change (failover), or the version store was aggressively cleaned up while a query was still referencing older row versions. Essentially, the data snapshot your query depends on no longer exists because the system determined it was safe to reclaim.

Step-by-Step Resolution Guide

Step 1: Identify the Long-Running Query and Replica Lag

  • Open SQL Server Management Studio (SSMS) and connect to the secondary replica.
  • Run SELECT * FROM sys.dm_exec_requests WHERE transaction_isolation_level = 5; to identify active snapshot isolation transactions.
  • Check SELECT * FROM sys.dm_hadr_database_replica_states; to confirm redo_queue_size and log_send_queue_size values are not excessively high.
  • Review the SQL Server Error Log at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for correlating failover or state-change timestamps.

Step 2: Adjust the Ghost Cleanup and Secondary Replica Settings

  • Increase the readable secondary’s tolerance for data lag by configuring REPLICA ON settings with SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY) and ensure applications aren’t querying stale snapshots indefinitely.
  • If using a readable secondary heavily for reporting, consider setting a controlled delay using ALTER AVAILABILITY GROUP [YourAGName] MODIFY REPLICA ON 'SecondaryReplicaName' WITH (SECONDARY_ROLE(READ_ONLY_ROUTING_LIST=(...))); to manage load distribution.
  • Evaluate disabling automatic failover temporarily during known heavy batch/reporting windows via ALTER AVAILABILITY GROUP [YourAGName] MODIFY REPLICA ON 'ReplicaName' WITH (FAILOVER_MODE = MANUAL);.

Step 3: Reduce Long-Running Transaction Duration

  • Break up large reporting queries or batch jobs into smaller transactions to reduce the window where old row versions are needed.
  • Add OPTION (RECOMPILE) or indexing improvements to speed up query execution time on the secondary.
  • Avoid wrapping read-only reporting queries in unnecessarily long explicit transactions (BEGIN TRAN...COMMIT).

Step 4: Validate Network and Replica Health

  • Open Server Manager > Tools > Windows Admin Center (or Failover Cluster Manager) on Windows Server 2025 to confirm no unexpected failovers or cluster resource flapping is occurring.
  • Check Event Viewer > Applications and Services Logs > MSSQLSERVER for Event ID 3948 correlated with Event ID 1135 (cluster resource state change) or Always On Health events (Event ID 1480).
  • Confirm stable network latency between primary and secondary replicas using ping and Test-NetConnection in PowerShell to rule out connectivity-induced redo delays.

Step 5: Apply Latest Cumulative Update

  • Verify your SQL Server 2025 build number via SELECT @@VERSION;.
  • Download and apply the latest Cumulative Update (CU) from the Microsoft Update Catalog, as ghost cleanup and version store interactions with Always On have received stability fixes in past CUs.
  • Reboot the Windows Server 2025 instance after patching to ensure the SQL Server service fully reinitializes.

Alternative or Verification Steps

  • To verify the fix, rerun the previously failing report/query on the secondary replica during a period of similar transactional load and confirm it completes without Error 3948 in the Application log or SSMS output.
  • If the issue persists, as an alternative, redirect read-only workloads temporarily to the primary replica (if licensing/performance allows) or implement Query Store/Resource Governor to throttle long-running snapshot queries until replica lag stabilizes.