通过睡眠状态会话而不是重述空间，tempdb日志文件的使用量很大。

Question

我们有一个在90%的驱动器级别上使用tempdb的警报阈值，并且收到了大量的警报。但存在的第一个问题是，在日志文件中使用了整个500 on的驱动器，仅占95%，无法增加对tempdb上其他工作的日志文件。当我们检查打开的事务时，我们看到休眠会话在时间戳前2天和1天在那里，并且没有将空间释放给其他事务。我认为这是最糟糕的行为，睡眠期堵塞了空间，并释放给其他人。我们需要脚本，因为那些会话占用日志文件空间，而不是释放它们。(我了解到jdbc驱动程序会话突然下降，因此睡眠会话不会将空间释放给其他事务)。你能提供是否有任何脚本来识别相同的。

Answer 1

使用此脚本确定每个会话的tempDB使用情况：

--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

Answer 2

安装和运行SP_WhoIsActive应该能够告诉您哪些进程具有打开的事务，以及它们正在使用多少TEMPDB空间。休眠但未提交的事务在提交或回滚之前不会释放空间。

Answer 3

dbcc opentran(tempdb)返回什么？这应该会告诉竞争中的spid。

dbcc inputbuffer( spid )应该为您提供spid正在运行的内容。

Mickael建议的sp_whoisactive将为您提供活动事务。

希望这能有所帮助。