We have a alert threshold of tempdb usage on drive level at 90% and getting lot of alerts. But the first problematic point is the tempdb usage occupied whole drive of 500GB among Log file only occupied 95% and not able to increase the log file for other works on tempdb. While we checked the open transactions we seen sleeping sessions are there 2days and 1day before time stamps and not released the space to other transactions. I think it's worst behavior the sleeping sessions blocked the space and released to the others. We need the script for Is really those sessions are occupieing the LOG file space and not released them. (I came to know jdbc drivers sessions suddenly drops and so that the sleeping sessions are not released the space to other transactions). Can you provide Is there any script to identify the same.
Use this script to determine tempDB usage per sessions :
--Lists the TempDB usage per each active session. --It helps identifying the sessions that use the tempdb heavily with internal objects. ;WITH task_space_usage AS ( -- SUM alloc/delloc pages SELECT session_id, request_id, SUM(internal_objects_alloc_page_count) AS alloc_pages, SUM(internal_objects_dealloc_page_count) AS dealloc_pages FROM sys.dm_db_task_space_usage WITH (NOLOCK) WHERE session_id <> @@SPID GROUP BY session_id, request_id ) SELECT TSU.session_id, TSU.alloc_pages * 1.0 / 128 AS [internal object MB space], TSU.dealloc_pages * 1.0 / 128 AS [internal object dealloc MB space], EST.text, -- Extract statement from sql text ISNULL( NULLIF( SUBSTRING( EST.text, ERQ.statement_start_offset / 2, CASE WHEN ERQ.statement_end_offset < ERQ.statement_start_offset THEN 0 ELSE( ERQ.statement_end_offset - ERQ.statement_start_offset ) / 2 END ), '' ), EST.text ) AS [statement text], EQP.query_plan FROM task_space_usage AS TSU INNER JOIN sys.dm_exec_requests ERQ WITH (NOLOCK) ON TSU.session_id = ERQ.session_id AND TSU.request_id = ERQ.request_id OUTER APPLY sys.dm_exec_sql_text(ERQ.sql_handle) AS EST OUTER APPLY sys.dm_exec_query_plan(ERQ.plan_handle) AS EQP WHERE EST.text IS NOT NULL OR EQP.query_plan IS NOT NULL ORDER BY 3 DESC, 5 DESC
Installing and running SP_WhoIsActive should be able to tell you what processes have open transactions and how much TEMPDB space they are using. A sleeping, but uncommitted transaction will not release the space until committed or rolled back
what does dbcc opentran(tempdb) return ? This should tell the spid in contention.
dbcc inputbuffer(spid) should give you what the spid is running.
sp_whoisactive as suggested by Mickael will give you the active transactions.
hope this helps.