You can check current space used by sessions in tempdb using sys.dm_db_task_space_usage.
For example, I use the following code to check out the current tempdb space usage:
CREATE view [sav].[vw_tempdb_usage]
as
select session_id,
cast(sum(internal_objects_alloc_page_count) * 8. /1024 /1024 as decimal(10,2))as internal_objects_alloc_Gb,
cast(sum(internal_objects_dealloc_page_count) * 8. /1024 /1024 as decimal(10,2))as internal_objects_dealloc_Gb,
cast(sum(user_objects_alloc_page_count) * 8. /1024 /1024 as decimal(10,2))as user_objects_alloc_Gb,
cast(sum(user_objects_dealloc_page_count) * 8. /1024 /1024 as decimal(10,2))as user_objects_dealloc_Gb,
cast(sum(internal_objects_alloc_page_count -
internal_objects_dealloc_page_count) * 8. /1024 /1024 as decimal(10,2))as internal_objects_diff_Gb,
cast(sum(user_objects_alloc_page_count -
user_objects_dealloc_page_count)* 8. /1024 /1024 as decimal(10,2)) as user_objects_diff_Gb
from sys.dm_db_task_space_usage
group by session_id
--having sum(internal_objects_alloc_page_count - internal_objects_dealloc_page_count +
-- user_objects_alloc_page_count - user_objects_dealloc_page_count) /1024 > 0
select
p.spid,
db_name(p.dbid) as db,
p.loginame,
p.hostname,
p.program_name,
p.login_time,
v.internal_objects_diff_Gb,
v.user_objects_diff_Gb,
p.cmd,
txt.text as txt,
CASE WHEN encrypted = 1 THEN '-- ENCRYPTED'
WHEN p.stmt_start >= 0
THEN substring(txt.text, stmt_start/2 + 1,
CASE stmt_end
WHEN -1 THEN datalength(txt.text)
ELSE stmt_end/2 - stmt_start/2 + 1
END)
END AS Statement
from s1057.sav.vw_tempdb_usage v join sys.dm_exec_sessions s
on v.session_id = s.session_id
join sys.sysprocesses p
on v.session_id = p.spid
cross apply sys.dm_exec_sql_text(p.sql_handle) txt
where v.internal_objects_diff_Gb + v.user_objects_diff_Gb > 0
Here I created a view which counts the space used by user objects and system objects in Gb, then I run a code adainst this view that shows me all sessions with their executing statements that use more than 10Mb, expressed in Gb (2 digits after point);
I put this code in a job that executes every 5 minutes and so I'm able to catch sessions that run out of space (we have 100Gb fixed tempdb space that is sufficient for our work, everything that runs out of this is anomal and must be investigated. To fill out 100Gb it takes more than 5 minutes so I'm always able to see who it was even if it happens off work hours)