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.
Click through to see David’s results and explanation.