The general code Paul S. Randal wrote to find space-hogs in tempdb

space-hogs-in-tempdb.sql

-- InternalMB/Pages: worktables (cursor, spool) , workfiles (hash joins), sort
-- UserMB/Pages: everything else
--
SELECT 
	GETDATE () AS [Date],
	[tsu].[session_id] AS [SessionID],
	[tsu].[exec_context_id] AS [ExecContextID], -- anything > 0 means parallelism
	([tsu].[user_objects_alloc_page_count] - 
		[tsu].[user_objects_dealloc_page_count]) AS [UserPages],
	ROUND (CONVERT (FLOAT, ([tsu].[user_objects_alloc_page_count] -
		[tsu].[user_objects_dealloc_page_count]) * 8) / 1024.0, 2) AS [UserMB],
	([tsu].[internal_objects_alloc_page_count] - 
		[tsu].[internal_objects_dealloc_page_count]) AS [InternalPages],
	ROUND (CONVERT (FLOAT, ([tsu].[internal_objects_alloc_page_count] -
		[tsu].[internal_objects_dealloc_page_count])
* 8) / 1024.0, 2) AS [InternalMB],
	[er].[plan_handle] AS [Plan],
	[est].[text] AS [Text]
FROM
	sys.dm_db_task_space_usage [tsu]
	JOIN sys.dm_exec_requests [er]
		ON [er].[session_id] = [tsu].[session_id]
	CROSS APPLY sys.dm_exec_sql_text ([er].[sql_handle]) [est]
/*
WHERE
	-- Optionally, filter by a size limit
	-- E.g., the 16384 is 128MB in 8KB pages
	(([user_objects_alloc_page_count] - [user_objects_dealloc_page_count]) +
		([internal_objects_alloc_page_count] -
			[internal_objects_dealloc_page_count])) >= 16384
*/
ORDER BY
	(([user_objects_alloc_page_count] - [user_objects_dealloc_page_count]) +
		([internal_objects_alloc_page_count] -
		[internal_objects_dealloc_page_count])) DESC;