InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 14:19:26 GMT20http://www.sqlservercentral.com/Forums/Topic1391687-2846-1.aspxIn case this is helpful to anybody, I have a similar query a to identify processes actively running that take up Tempdb space.I found this useful as sometimes we would have extremely large stored procedures with lots of nested procedures that were taking up too much tempDb space and this helped identify the specific query text that was the culprit.USE tempdbSELECT query_text= coalesce((SELECT SUBSTRING(text, t2.statement_start_offset/2 + 1, (CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max),text)) * 2 ELSE statement_end_offset END - t2.statement_start_offset)/2) FROM sys.dm_exec_sql_text(t2.sql_handle)) , 'Not currently executing') , t1.session_id , t1.request_id , task_alloc_GB = cast((t1.task_alloc_pages * 8./1024./1024.) as numeric(10,1)) , task_dealloc_GB = cast((t1.task_dealloc_pages * 8./1024./1024.) as numeric(10,1)) , host= case when t1.session_id =50 then 'SYS' else s1.host_name end , s1.login_name , s1.status , s1.last_request_start_time , s1.last_request_end_time , s1.row_count , s1.transaction_isolation_level ,t2.plan_handle , query_plan=(SELECT query_plan from sys.dm_exec_query_plan(t2.plan_handle)) from (Select session_id, request_id , task_alloc_pages=sum(internal_objects_alloc_page_count + user_objects_alloc_page_count) , task_dealloc_pages = sum (internal_objects_dealloc_page_count + user_objects_dealloc_page_count) from sys.dm_db_task_space_usage group by session_id, request_id) as t1 left join sys.dm_exec_requests as t2 on t1.session_id = t2.session_id and t1.request_id = t2.request_id left join sys.dm_exec_sessions as s1 on t1.session_id=s1.session_id where t1.session_id &lt;&gt; 50 -- ignore system and t1.session_id &lt;&gt; @@SPID -- ignore this request itself AND s1.status = 'running' order by t1.task_alloc_pagesTue, 04 Dec 2012 06:50:51 GMTjames.anderson 82942http://www.sqlservercentral.com/Forums/Topic1391687-2846-1.aspxComments posted to this topic are about the item [B]<A HREF="/scripts/tempdb/94828/">Get space used by queries in tempdb</A>[/B]Sat, 01 Dec 2012 19:59:05 GMTharsha.majety