tempdb log file usage huge by sleeping status sessions but not relased the space

Question

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.

Answer 1

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

• Narendra: Thanks for the script and we have modified the above script and scheduled it through job but it's not giving log file sessions related usage, It's giving internal and external objects which are using the MDF file space. But we are looking for LDF file usage related stuff. You can go through the another commend in same page.

Answer 2

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

Answer 3

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.

• Narendra: I used the above as initial check list and when we fired the dbcc opentran() got the spid as open tran and the status is in sleeping state, After that we used to inputbuffer(spid) and got the 'FETCH_CURSOR 001010001' command with it. Strangely the sleeping sessions are there older than 2days once we killed them can able to shrink the log file. So we need a script for identifying such type of sp's proactively before we are going face those problems.
• Marcello Miorelli: @Narendra you did not accept any answer?