Right now we use the following query to find out which abnormally stay in the tempdb for a long time and keep use up tempdb's log file space.
SELECT spid, loginame, program_name, hostname as "From host", login_time, last_batch, DB_NAME(dbid) AS "Against database" ,
(SELECT text FROM sys.dm_exec_sql_text(sql_handle))as "Query executed"
FROM master..sysprocesses WHERE loginame= 'yyy' and open_tran > 0
AFtre we found one then we will kill <spid> to kill the query.
But this script is not perfect enought, how can we change it to also include the tempdb storage space/size the eat up by the query shown in the output ?