Introduction SQL Server uses tempdb to store row versions when snapshot isolation or READ COMMITTED SNAPSHOT is enabled. Long-running transactions under these isolation levels cause the version store to grow, consuming tempdb space and creating I/O contention that affects all database operations.

Symptoms - `tempdb` database growing continuously, consuming all available disk space - `sys.dm_db_file_space_usage` shows `version_store_reserved_page_count` extremely high - Queries running slowly with `PAGELATCH` or `PAGELATCH_EX` waits on tempdb files - Error 1105: `Could not allocate space for object in tempdb` - All databases affected since tempdb is shared across the instance

Common Causes - Long-running transactions under snapshot isolation holding version store entries - `READ_COMMITTED_SNAPSHOT` enabled with long-running read queries - tempdb configured with a single data file causing allocation contention - Abandoned transactions not being committed or rolled back - Reporting queries running under snapshot isolation during peak hours

Step-by-Step Fix 1. **Check version store usage": ```sql -- Version store size SELECT SUM(version_store_reserved_page_count) * 8 / 1024 AS version_store_mb, SUM(internal_object_reserved_page_count) * 8 / 1024 AS internal_object_mb, SUM(user_object_reserved_page_count) * 8 / 1024 AS user_object_mb FROM sys.dm_db_file_space_usage;

-- Find the longest-running transactions using version store SELECT transaction_id, transaction_sequence_num, commit_sequence_num, is_snapshot, first_snapshot_sequence_num FROM sys.dm_tran_active_snapshot_database_transactions ORDER BY elapsed_time_seconds DESC; ```

  1. 1.**Identify and kill long-running transactions":
  2. 2.```sql
  3. 3.SELECT
  4. 4.s.session_id,
  5. 5.s.login_name,
  6. 6.s.host_name,
  7. 7.t.transaction_id,
  8. 8.t.transaction_begin_time,
  9. 9.DATEDIFF(second, t.transaction_begin_time, GETDATE()) AS duration_seconds,
  10. 10.r.command,
  11. 11.r.status
  12. 12.FROM sys.dm_tran_active_transactions t
  13. 13.JOIN sys.dm_exec_sessions s ON s.session_id = t.transaction_id
  14. 14.LEFT JOIN sys.dm_exec_requests r ON r.session_id = s.session_id
  15. 15.WHERE DATEDIFF(second, t.transaction_begin_time, GETDATE()) > 300
  16. 16.ORDER BY t.transaction_begin_time;

-- Kill the offending session KILL <session_id>; ```

  1. 1.**Add tempdb data files to reduce allocation contention":
  2. 2.```sql
  3. 3.-- Add files to match CPU core count (up to 8)
  4. 4.ALTER DATABASE tempdb ADD FILE (
  5. 5.NAME = N'tempdb2',
  6. 6.FILENAME = N'E:\SQLData\tempdb2.ndf',
  7. 7.SIZE = 8GB,
  8. 8.FILEGROWTH = 512MB
  9. 9.);

-- Enable trace flag 1118 for uniform extent allocation DBCC TRACEON(1118, -1); ```

  1. 1.**Enable optimistic locking hints for specific queries":
  2. 2.```sql
  3. 3.-- Instead of enabling RCSI database-wide, use query hints
  4. 4.SELECT * FROM orders WITH (NOLOCK) WHERE status = 'pending';

-- Or use snapshot isolation only for specific transactions SET TRANSACTION ISOLATION LEVEL SNAPSHOT; BEGIN TRAN; SELECT * FROM orders WHERE order_date > '2026-01-01'; COMMIT; ```

Prevention - Monitor version store size with alerting at 50% of tempdb capacity - Keep transactions short under snapshot isolation - Configure tempdb with multiple equally-sized data files (1 per CPU core, max 8) - Set appropriate autogrowth (512MB fixed growth, not percentage) - Use `READ COMMITTED` instead of `READ_COMMITTED_SNAPSHOT` for reporting workloads - Implement query timeouts to prevent runaway transactions - Schedule long-running reports during off-peak hours