Troubleshooting Guide: Error 3967 – Insufficient Space in tempdb Version Store

Why This Error Happens

This error occurs when the tempdb version store grows faster than SQL Server 2025 can reclaim space, typically because tempdb has run out of free disk space or has hit its configured maximum file size growth limit. Long-running transactions, uncommitted transactions, or heavy snapshot isolation/read-committed snapshot isolation (RCSI) usage prevent old row versions from being cleaned up, causing the version store to balloon. When SQL Server can no longer allocate space for new versions, it marks the oldest/largest offending transactions as victims and rolls them back to free up space.

Step-by-Step Resolution Guide

Step 1: Identify the Transactions Blocking Version Store Cleanup

• Run the following query to find long-running or orphaned transactions holding onto version store entries:

SELECT session_id, transaction_id, elapsed_time_seconds, database_id
FROM sys.dm_tran_active_snapshot_database_transactions
ORDER BY elapsed_time_seconds DESC;

• Cross-reference session_id with sys.dm_exec_sessions and sys.dm_exec_requests to identify the application or query causing the long transaction.
• Look for sessions in a “sleeping” state with open transactions — these are prime candidates for manual intervention.

Step 2: Free Up tempdb Space Immediately

• Kill any identified long-running/idle transactions using KILL <session_id>; after confirming it is safe to terminate.
• Check current tempdb space usage:

SELECT SUM(unallocated_extent_page_count) AS free_pages
FROM sys.dm_db_file_space_usage;

• If tempdb data files are undersized, add or expand tempdb files via SSMS or T-SQL:

ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev, SIZE = 20480MB, FILEGROWTH = 1024MB);

• Ensure the drive hosting tempdb (commonly D:\SQLData\tempdb.mdf or a dedicated SSD volume) has adequate free disk space — at least 20-30% headroom.

Step 3: Reconfigure tempdb Layout and Growth Settings

• Distribute tempdb across multiple data files (one per CPU core, up to 8) to reduce contention and improve throughput:

ALTER DATABASE tempdb 
ADD FILE (NAME = tempdev2, FILENAME = 'E:\SQLData\tempdb2.ndf', SIZE = 8192MB, FILEGROWTH = 1024MB);

• Set a fixed, generous initial size with moderate autogrowth increments (avoid percentage-based growth) to prevent fragmentation and allocation stalls.
• Move tempdb files to a dedicated high-speed disk (NVMe/SSD) separate from user databases if not already configured.

Step 4: Review and Adjust Isolation Level Usage

• Audit applications using snapshot isolation or RCSI, as these are the primary consumers of the version store:

SELECT name, is_read_committed_snapshot_on, snapshot_isolation_state_desc
FROM sys.databases;

• Work with application teams to shorten transaction scope and commit transactions promptly, avoiding open transactions during user think-time or batch waits.
• Consider enabling trace flag 3427 or adjusting application-level retry logic to handle transient victim rollbacks gracefully.

Alternative or Verification Steps

• Verify the fix by monitoring sys.dm_db_file_space_usage and the version store size over 24-48 hours to confirm it stabilizes rather than continuously growing.
• If the error persists after resizing tempdb, check Windows Server 2025 disk-level constraints (e.g., Storage Spaces quotas, VHDX dynamic disk limits) and consider migrating tempdb to a larger dedicated volume.