SELECT TOP 5 --Change number accordingly su.session_id AS 'td','', ss.Login_Name AS 'td','', rq.Command AS 'td','', su.Task_Alloc AS 'td','', su.Task_Dealloc AS 'td','', --Find Offending Query Text: (SELECT SUBSTRING(text, rq.statement_start_offset/2 + 1, (CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max),text)) * 2 ELSE statement_end_offset END - rq.statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS 'td' FROM (SELECT su.session_id, su.request_id, SUM(su.internal_objects_alloc_page_count + su.user_objects_alloc_page_count) AS Task_Alloc, SUM(su.internal_objects_dealloc_page_count + su.user_objects_dealloc_page_count) AS Task_Dealloc FROM sys.dm_db_task_space_usage AS su GROUP BY session_id, request_id) AS su, sys.dm_exec_sessions AS ss, sys.dm_exec_requests AS rq WHERE su.session_id = rq.session_id AND(su.request_id = rq.request_id) AND (ss.session_id = su.session_id) AND su.session_id > 50 --sessions 50 and below are system sessions and should not be killed AND su.session_id <> (SELECT @@SPID) --Eliminates current user session from results ORDER BY su.task_alloc DESC --The largest "Task Allocation/Deallocation" is probably the query that is causing the db growth
