Troubleshooting Guide: SQL Server Error 3959 – Version Store Full

Why This Error Happens

Error 3959 occurs when the version store in tempdb runs out of space because it fills up faster than SQL Server’s cleanup process can remove old row versions. This is typically caused by long-running active transactions that prevent version cleanup, an undersized or misconfigured tempdb, or a high volume of data modifications combined with Snapshot Isolation or Read Committed Snapshot Isolation (RCSI) being enabled on one or more databases. Essentially, tempdb cannot keep pace with the rate of versioned row generation, causing the version store to exceed available space and forcing SQL Server to reject new version entries.

Step-by-Step Resolution Guide

Step 1: Identify Long-Running or Orphaned Transactions

• Run the following query to find transactions holding open the version store:

  SELECT * FROM sys.dm_tran_active_snapshot_database_transactions;

• Look for transactions with a high elapsed_time_seconds value or a session_id that has been idle for an extended period.
• Kill problematic sessions using KILL <session_id>; only after confirming they are safe to terminate.

Step 2: Resize and Reconfigure tempdb

• Navigate to SQL Server Management Studio (SSMS) > Databases > System Databases > tempdb > Properties > Files.
• Increase the initial size of tempdb data files (e.g., set to 4096 MB or higher depending on workload).
• Ensure multiple tempdb data files are configured (ideally one per logical CPU core, up to 8) to reduce contention:

  ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, SIZE = 4096MB, FILEGROWTH = 512MB);

• Place tempdb files on a dedicated, high-speed SSD volume (e.g., E:\SQLData\TempDB\) separate from user databases.

Step 3: Review and Adjust Isolation Level Usage

• Check which databases have Snapshot Isolation or RCSI enabled:

  SELECT name, snapshot_isolation_state_desc, is_read_committed_snapshot_on 
  FROM sys.databases;

• If not strictly required, consider disabling RCSI on non-critical databases to reduce version store pressure:

  ALTER DATABASE [YourDB] SET READ_COMMITTED_SNAPSHOT OFF;

• Audit application code for transactions left open without a COMMIT or ROLLBACK.

Step 4: Monitor tempdb Version Store Usage

• Use Performance Monitor (PerfMon) on Windows Server 2025 to track the Version Store Size (KB) counter under the SQLServer:Transactions object.
• Set up an alert threshold (e.g., 80% of allocated tempdb space) to proactively catch future growth before it causes failures.

Step 5: Restart the SQL Server Service (If Version Store is Stuck)

• If the version store remains full even after cleanup, perform a controlled restart via Services.msc or PowerShell:

  Restart-Service -Name 'MSSQLSERVER' -Force

• This clears tempdb entirely, as it is recreated on every SQL Server startup.

Alternative or Verification Steps

• Verify the fix by re-running SELECT * FROM sys.dm_tran_active_snapshot_database_transactions; and confirming the result set is empty or shows minimal active transactions with low elapsed time.
• If the error persists under normal workload after resizing tempdb, consider isolating heavy batch/ETL jobs to run during off-peak hours, or review third-party applications (e.g., CDC, replication) that may hold long transactions open against the version store.