We all had those calls, it’s the middle of the night and we get that phone call that a customer is getting error messages due to their tempDB being full.
My first step is usually going to be to have a look and see what sessions have allocations in TempDB, we can do that quickly and easily using the following query.
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
Imagine though that the query comes back and you can’t see any allocations that would account for the space that’s being taken up in TempDB? Strange right?
Well, there is something else that I’ve seen have a habit of filling TempDB. If you’re using Read Committed Snapshot or RCSI then you’ll also have a version store in your database. I’m not going to go into the details of exactly how version store works, there is plenty of documentation on it out there if you’re interested (perhaps I’ll write a post on it sometime).
The key bit that you need to know is that although SQL will keep version store trimmed down and only keep the rows that are needed, it can only clear rows that are older than the oldest transaction. This is because SQL has no way of knowing what rows are going to be needed by the transaction. Do you see a potential issue here?
What’s going to happen if someone walks away and leaves a transaction open? Yup, you’ve got it! Version store is going to start filling up.
So how can we check for this?
I’ve got another little script that I tend to run when I can’t see any user allocations in TempDB.
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
So from that we can see exactly where the pages in TempDB are, in this case nearly all of it is sitting in version store.
Once I know that, the next thing that I want to know is if there are any long running sessions that could be preventing the version store from clearing down. We’ve got a nice little DMV that can help us with this, sys.dm_tran_active_snapshot_database_transactions
SELECT session_id, elapsed_time_seconds
FROM sys.dm_tran_active_snapshot_database_transactions
So it looks like session 86 is the pantomime villain here! Obviously execute usual caution when killing sessions, but I’ll happily bet that if you end that session you’ll find version store will clear down and your TempDB woes will be over. Well until someone does the same thing next time.
It’s quite a good idea to monitor TempDB so that you can spot these sorts of issues occurring before they bite you.
An upcoming version of our Undercover Inspector tool will monitor TempDB and alert to this sort of issue, so there’s no excuse to get caught out 