Script to find TEMP use by SPID in SQL Server

The script will list the temporary space use by SPID in SQL Server. This script is great to identify query's that consume allot of your temp files.

USE tempdb
Go

SELECT t.text,
      SUM(internal_objects_alloc_page_count) AS task_internal_objects_alloc_page_count,
      SUM(internal_objects_dealloc_page_count) AS task_internal_objects_dealloc_page_count,
      st.session_id
    FROM sys.dm_db_task_space_usage  st
	JOIN sys.sysprocesses sp
		ON sp.spid = st.session_id
	CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) t
    GROUP BY t.text,st.session_id
	ORDER BY 2 DESC


Leave a comment or a question in the comment area bellow if you fell like


Be a sport and

Search

Loading... Please wait

Subscribe to our Newsletter

Be a sport and

x