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,
           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],
       -- Extract statement from sql 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],
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
| improve this answer | |
  • 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. – Narendra Sep 9 '16 at 5:13

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

| improve this answer | |

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.

| improve this answer | |
  • 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. – Narendra Sep 9 '16 at 5:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.