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... – Aaron Bertrand Dec 2 '15 at 23:37
  • 1
    Also, what exactly do you mean the log is "locked up"? Are you trying to shrink it? Why? – Aaron Bertrand Dec 2 '15 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 Dec 2 '15 at 23:40
  • John, do you mean you have other sessions waiting on that one tempdb transaction? – Nicolas de Fontenay Dec 2 '15 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 Dec 2 '15 at 23:46

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.

| improve this answer | |
  • we stumbled across an extended event that nobody knew about called "Testing." - Testing should not be on PROD :-) – Kin Shah Dec 3 '15 at 22:54
  • Oh, things I know @Kin. If only there were a way to track who created it... – 8bit Dec 3 '15 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
| improve this answer | |
  • 2
    I think I know this query :-) – spaghettidba Dec 2 '15 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... – Aaron Bertrand Dec 2 '15 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 Dec 2 '15 at 23:58
  • 1
    The query is referenced from @AaronBertrand 's answer – Kin Shah Dec 3 '15 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. – Nicolas de Fontenay Dec 3 '15 at 18:40

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.