Locked TempDB Log

Question

So we're running into an issues where TempDB's log is currently locked up. After checking log_reuse_wait_desc, we see that it is locked from an active transaction. Now the weird part is that when we run DBCC OPENTRAN to get information on the query causing the lock, the command returns "No active open transactions". We've killed every spid connecting to TempDB and we're at the point where we'll be cycling the instance to resolve the issue. Does anyone understand what's happening here or if there's a better way to deal with this?

Answer 1

So we figured it out:

We rebooted the server and when it came back up, we were still having issues. We started re-considering whether it was just an issue with TempDB and turned to maybe trying to catch the issues with extended events. While looking into it, we stumbled across an extended event that nobody knew about called "Testing." We disabled that XE and, go figure, everything started working fine again. I'm not sure WHY this locked up everything in TempDB and we're looking into understanding the issue, but for now - the issue is resolved.

Answer 2

Can you see what's running in tempdb?

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