Technical Article

Get space used by queries in tempdb

,

Run script in tempdb . Can be used to troubleshoot tempdb space usage problems usually associated with large queries that use a lart amount of space for work tables and work 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 
    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
ORDER BY 2 DESC

Rate

2.33 (3)

You rated this post out of 5. Change rating

Share

Share

Rate

2.33 (3)

You rated this post out of 5. Change rating