Have you ever had an issue where TempDB was filling up on your secondary replicas?  Do those secondaries happen to be read only replicas?

When I have an issue with tempdb filling up the first thing that I usually do is try to figure out exactly what the space has been allocated to.

You can quickly figure out what process has the most space allocated by using a quick query against dm_db_session_space_usage.

SELECT session_id, database_id, user_objects_alloc_page_count + internal_objects_dealloc_page_count AS TotalAllocatedPages
FROM sys.dm_db_session_space_usage
ORDER BY TotalAllocatedPages DESC

But what if you can see that there aren’t any pages allocated to sessions?  What could be taking up all the space?  Well let’s have a little look and see exactly where those pages are allocated.

USE tempdb
SELECT SUM (version_store_reserved_page_count) AS Version_Store_Reserved,
SUM (user_object_reserved_page_count) AS User_Object_Reserverd,
SUM (internal_object_reserved_page_count) AS Internal_Object_Reserved,
SUM (mixed_extent_page_count) AS Mixed_Extent
FROM sys.dm_db_file_space_usage

Well looking at that, it would seem that all our space is being taken up by the version store.  But that doesn’t make a lot of sense, version store is used by snapshot isolation level and we’ve not got any database that use snapshot isolation?

Actually if you’re using read-only replicas, you are using snapshot isolation.  Any query run against a read-only replica actually uses snapshot isolation.

So now we need to know if we’ve got any sessions that could access the version store.  For that we can tap up another DMV, sys.dm_tran_active_snapshot_database_transactions.

SELECT session_id, elapsed_time_seconds
FROM sys.dm_tran_active_snapshot_database_transactions

Well, well so it appears that we do have transactions that are currently using the version store and look at spid 86!  That’s been running for 432000 seconds, that’s quite a long time.

So why would a long running query cause the version store to fill like that?  SQL will regularly clear out all the rows from the version store that are no longer needed, this will usually keep things under control.  There is a big BUT here though, SQL can only clear out rows that are older than the oldest transaction, this is because even if they’re in a different database, SQL has no way of knowing if that old transaction is going need those rows so it has to keep hold of them.  What that means for us is that if we’ve got a long running transaction, SQL is going to be unable to clear out the version store and eventually we’re going to see the version store grow out, which means that tempdb will also grow out.

In my case, as soon as I killed off that long running transaction, SQL Server was able to clear out the version store and the tempdb usage came back down to sensible levels.