TempDB Version Store bloated

What this check looks for

The version store’s reserved space, taken from sys.dm_db_file_space_usage, compared against the total size of tempdb’s data files only, excluding the log. If the version store is at 80 percent or more of that space, the check fires.

Why it matters

The version store is not a cache. It is a queue that cannot be cleaned up out of order.

Read Committed Snapshot Isolation and snapshot isolation work by keeping the old copy of a row whenever it is modified, so a reader can see the version that existed when its statement or transaction started. Those versions live in tempdb.

Cleanup runs regularly, but it can only remove versions older than the oldest active transaction on the instance. One transaction left open holds the whole queue open behind it, whether or not that transaction has anything to do with the data being versioned. A reporting query that has been running for two hours means two hours of every modification on the server is still sitting in tempdb.

At 80 percent the remaining headroom is measured in minutes on a busy system. When tempdb fills:

  • New versions cannot be generated, so modifications start failing.
  • Snapshot reads fail with error 3958 when the version they need was never written.
  • Everything else that uses tempdb, which is sorts, hashes, table variables, temporary tables and online index rebuilds, fails at the same time.

That is an instance-wide outage caused by a single query nobody was watching.

How to confirm it yourself

SELECT SUM(fsu.[version_store_reserved_page_count]) * 8 / 1024.0 AS [version_store_mb],
       SUM(fsu.[unallocated_extent_page_count])     * 8 / 1024.0 AS [free_mb],
       SUM(fsu.[user_object_reserved_page_count])   * 8 / 1024.0 AS [user_objects_mb],
       SUM(fsu.[internal_object_reserved_page_count]) * 8 / 1024.0 AS [internal_objects_mb]
  FROM tempdb.sys.dm_db_file_space_usage AS fsu;

Then find the transaction holding it, which is the whole point:

SELECT TOP (10)
       vs.[transaction_id],
       vs.[transaction_sequence_num],
       vs.[elapsed_time_seconds],
       s.[session_id], s.[login_name], s.[host_name], s.[program_name], s.[status]
  FROM sys.dm_tran_active_snapshot_database_transactions AS vs WITH (NOLOCK)
  LEFT JOIN sys.dm_exec_sessions AS s WITH (NOLOCK)
         ON s.[session_id] = vs.[session_id]
 ORDER BY vs.[elapsed_time_seconds] DESC;

And confirm which databases have versioning switched on at all:

SELECT [name], [is_read_committed_snapshot_on], [snapshot_isolation_state_desc]
  FROM sys.databases WITH (NOLOCK)
 WHERE [is_read_committed_snapshot_on] = 1
    OR [snapshot_isolation_state] <> 0;

How to fix it

Deal with the oldest transaction first. Everything else is secondary. The query above orders by elapsed time precisely because the top row is almost always the answer, and clearing it lets cleanup catch up on its own within a minute or two.

Then, in order of how long they take:

  • Find why that transaction was long. A reporting query against a busy OLTP database, a batch job with no batching, or an abandoned transaction. See the long open transaction check.
  • Give tempdb enough room. The version store is a legitimate consumer, and sizing tempdb for the workload rather than for an empty instance is not a workaround.
  • Reconsider where long reads run. A readable secondary or a separate reporting copy takes the long queries off the instance that has to keep the versions.
  • Check whether RCSI is switched on where it is not needed. It is a good default for most OLTP workloads, but it is not free, and turning it on across every database including ones with no read and write contention costs tempdb space for no benefit.

Restarting SQL Server clears tempdb and is the fastest way to make the symptom disappear. It does nothing about the cause, and the cause will be back within the day.

How long it takes

About an hour. Identifying and clearing the transaction is quick; deciding what to do about the query that caused it takes the rest.

Report Why you would go there
TempDB Consumers What is using tempdb, broken down by consumer.
TempDB High Usage The queries generating the most tempdb work.
Open Transactions The long transaction holding the version store open.
TempDB Allocation Whether tempdb is sized and laid out correctly to begin with.
Long Running History Whether this is one bad query or a recurring pattern.
Check
A transaction has been open far too long The usual cause, on its own terms.
TempDB showing growth Tempdb growing rather than being sized for the workload.
TempDB only has a single data file A separate tempdb problem that often appears alongside.

Frequently asked questions

We do not use snapshot isolation. Why is there a version store? Several features use it without being asked for: online index rebuilds, triggers, and multiple active result sets. The version store is never empty on a working instance.

Cleanup is not running. Can I force it? No, and it is almost certainly running fine. It cannot remove anything newer than the oldest active transaction, so what looks like cleanup failing is nearly always one long transaction.

Should I turn RCSI off? Not as a response to this. RCSI usually solves more problems than it creates. Turn it off only for databases that were switched on without a reason.

Adding tempdb files will fix it? More files help with allocation contention, which is a different problem. For this, size is what matters, not file count.