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?

  • 2
    What do you see in sys.dm_tran_active_transactions? Just because DBCC OPENTRAN returns 0 rows doesn't mean no transactions exist. Try this: USE msdb; BEGIN TRANSACTION; SELECT * INTO #foo FROM dbo.sysjobs; Now try DBCC OPENTRAN... Commented Dec 2, 2015 at 23:37
  • 1
    Also, what exactly do you mean the log is "locked up"? Are you trying to shrink it? Why? Commented Dec 2, 2015 at 23:38
  • This will remove all cache plans,etc from buffer pool... So use it with caution.Before restarting try DBCC FREEPROCCACHE -- clean cache DBCC DROPCLEANBUFFERS -- clean buffers DBCC FREESYSTEMCACHE ('ALL') -- clean system cache DBCC FREESESSIONCACHE -- clean session cache. Also, check sessions using sys.dm_exec* dmv.
    – Kin Shah
    Commented Dec 2, 2015 at 23:40
  • John, do you mean you have other sessions waiting on that one tempdb transaction? Commented Dec 2, 2015 at 23:45
  • @AaronBertrand Locked up was probably a poor term to use, rather, it's not re-using space. The TempDB files are all set sizes with no auto-growth and users are getting errors when running queries that use temp tables. We're not trying to Shrink it, I'm just trying to figure out why we cant find whatever spid is locking it up. I ran the query you mentioned above and I'm still getting nothing in DBCC OPENTRAN :/
    – 8bit
    Commented Dec 2, 2015 at 23:46

2 Answers 2


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.

  • we stumbled across an extended event that nobody knew about called "Testing." - Testing should not be on PROD :-)
    – Kin Shah
    Commented Dec 3, 2015 at 22:54
  • Oh, things I know @Kin. If only there were a way to track who created it...
    – 8bit
    Commented Dec 3, 2015 at 22:56

Can you see what's running in tempdb?

;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
  • 2
    I think I know this query :-) Commented Dec 2, 2015 at 23:50
  • 2
    This will only return active requests, not those that are idle/sleeping. This could be a BEGIN TRANSACTION; SELECT ...; that "finished" last Tuesday, and it won't show up in sys.dm_exec_requests... Commented Dec 2, 2015 at 23:50
  • Like mentioned above, all this returns are some of the active transactions we can track - none of which seem to be causing the lock (Just a few spids that have kicked off in the past hour or so)
    – 8bit
    Commented Dec 2, 2015 at 23:58
  • 1
    The query is referenced from @AaronBertrand 's answer
    – Kin Shah
    Commented Dec 3, 2015 at 4:15
  • Yes I had found it here. Is that yours spaghettidba? I have seen a few good things from you peppered across dba.stackexchange. I should read all comments first. @AaronBertrand I had to use it a lot when we struggled with ASP State database causing pain in tempdb. Commented Dec 3, 2015 at 18:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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