TempDB Log Won't Shrink No Transactions Running AlwaysON AG

  • I have an issue with one of my clusters when TEMPDB Log won't shrink and keeps growing until reset. Happens randomly about once a month and have to reset eventually although the growth is gradual(2GB a day).

    OS: Windows 2012 R2(Patched up to date)

    SQL : 11.0.6020

    Cluster Setup: Multi-Subnet with 2 Syncronous Physical in Primary and 1 ASYNC Virtual in secondary DataCenter

    *TempDB/TempLog is on local SSD while other DATA on Shared SAN.

    *I have run checkpoint

    *I have run dbcc freeproccache

    *Log_resuse_wait is ACTIVE_TRANSACTION

    The only active transaction is sp_server_diagnostics which I used multiple queries to make sure(sp_whoisactive and below query and a few more just to be sure). I killed that but this is for the isAlive heartbeat check and comes right back and still can't clear log

    Any help would be much appreciated!

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

  • What is the output you get when you run:

    DBCC OPENTRAN

    Sue

  • Actually dbcc opentran could still not give you the results you need since it's for the current database only.

    Did you query sysprocesses where open_tran >0

    I would also try this script instead to see the query text of any open transactions:

    http://www.sqlskills.com/blogs/paul/script-open-transactions-with-text-and-plans/

    Sue

  • Thanks very much the Paul Randal led me to a stray process that didn't show up as an active transaction. Issue resolved!

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply