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

by

Troubleshooting Guide: Error 3966 – Transaction Rolled Back Due to Version Store Shrinkage

Why This Error Happens

Error 3966 occurs when tempdb runs out of available space, forcing SQL Server to shrink the version store that supports Snapshot Isolation or Read Committed Snapshot Isolation (RCSI). Any long-running transaction that still required an old row version for consistency is marked as a “victim” and subsequently rolled back once it attempts to access that now-purged version. This is fundamentally a resource-pressure issue in tempdb, often caused by undersized tempdb files, long-running transactions holding versions open, or heavy concurrent workloads generating excessive row versioning data.

Step-by-Step Resolution Guide

Step 1: Increase and Pre-Size tempdb Capacity

  • Open SQL Server Management Studio (SSMS) and connect to the instance.
  • Navigate to Server Properties > Database Settings, or run a T-SQL script to modify tempdb file sizes:
  ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, SIZE = 10240MB, FILEGROWTH = 1024MB);
  • Ensure tempdb data files reside on a fast, dedicated drive (ideally NVMe/SSD) separate from OS and user databases on Windows Server 2025.
  • Restart the SQL Server service for file size changes to take effect if autogrowth events were previously exhausting disk space.

Step 2: Identify and Terminate Long-Running Transactions

  • Run the following query to find transactions holding open snapshots:
  SELECT session_id, elapsed_time_seconds, database_id 
  FROM sys.dm_tran_active_snapshot_database_transactions
  ORDER BY elapsed_time_seconds DESC;
  • Correlate session_id with sys.dm_exec_requests to identify the offending application or query.
  • Work with application owners to shorten transaction scope, or manually kill the session using KILL <session_id> if it is safe to do so.

Step 3: Monitor and Adjust tempdb Version Store Usage

  • Enable monitoring via Performance Monitor (PerfMon) on Windows Server 2025, tracking the SQLServer:Transactions > Version Store Size (KB) counter.
  • Query sys.dm_tran_version_store_space_usage to identify which database is consuming the most version store space:
  SELECT database_id, reserved_page_count * 8 AS space_kb 
  FROM sys.dm_tran_version_store_space_usage;
  • If a single database is disproportionately responsible, consider isolating heavy RCSI/Snapshot workloads to a dedicated instance or adjusting isolation levels where strict snapshot consistency is not required.

Step 4: Add Multiple tempdb Data Files

  • Configure tempdb with multiple equally-sized data files (typically matching the number of logical CPU cores, up to 8) to reduce allocation contention that can exacerbate space pressure.
  • Example command to add a secondary file:
  ALTER DATABASE tempdb ADD FILE (NAME = tempdev2, FILENAME = 'D:\TempDB\tempdev2.ndf', SIZE = 10240MB, FILEGROWTH = 1024MB);
  • Verify file layout using sys.master_files to confirm even distribution.

Alternative or Verification Steps

  • Verify the fix: Re-run the previously failing workload during peak concurrency and monitor sys.dm_tran_version_store_space_usage to confirm tempdb no longer approaches capacity limits (check free space via sys.dm_os_volume_stats).
  • Alternative solution: If space constraints persist despite tuning, temporarily disable Snapshot Isolation/RCSI on the affected database (ALTER DATABASE [DBName] SET ALLOW_SNAPSHOT_ISOLATION OFF;) to stop version generation while you address the underlying storage capacity on Windows Server 2025.