How to find the root cause of tempdb log file increasing

  • Hi all,

    I found the tempdb log file size of one instance increased to 200 GB.

    how can i get the root cause of the issue?

    any answer is appreciated.

    thanks.

  • Wison (8/22/2014)


    Hi all,

    I found the tempdb log file size of one instance increased to 200 GB.

    how can i get the root cause of the issue?

    any answer is appreciated.

    thanks.

    Quick suggestion, look into sys.dm_tran_* dynamic management views. There are quite few good articles around such as this one Investigating Transactions Using Dynamic Management Objects[/url]

    😎

  • This was removed by the editor as SPAM

  • jacksonandrew321 (8/23/2014)


    Wison (8/22/2014)


    Hi all,

    I found the tempdb log file size of one instance increased to 200 GB.

    how can i get the root cause of the issue?

    any answer is appreciated.

    thanks.

    You can reduce the tempdb size by using T-SQL, DBCC shrinkDataBase and DBCC shrinkFile. For more information about these visit : http://support.microsoft.com/kb/307487

    actually now the status of tempdb vlf is 2. and i cannot shrink it until the oldest active transaction releases.

    so, i only want to get some ideas about how to find the root cause of tempdb log file increased to so large.

  • I would not suggest you to use DBCC ShrinkDatabase, but should see the query which is creating this.

    In one of my projects I observed such a behaviour when I was using a bulk delete which caused log file to increase tremendously.

    Try to use fn_dblog and try to find which transaction created maximum log

  • In your case, the reason mite be some long running queries still waiting to commit or mite be blocking. So, as u mentioned in the earlier post that still active transactions are present in ur VLF rite, try to check what operations the Open Transactions are firing. Hope this helps. if u still Unable to find the issue, i can give u more suggestions as i faced similar issues in the past.

  • er.mayankshukla (8/23/2014)


    I would not suggest you to use DBCC ShrinkDatabase, but should see the query which is creating this.

    In one of my projects I observed such a behaviour when I was using a bulk delete which caused log file to increase tremendously.

    Try to use fn_dblog and try to find which transaction created maximum log

    Quick question, have you looked into the sys.dm_tran* views to identify the transaction activities?

    😎

  • Yes, but dmv's like dm_tran_active_transactions Or dm_tran_database_transactions

    do not provided me the log length which I require to see which transaction is making my log size to increase.

  • er.mayankshukla (8/23/2014)


    Yes, but dmv's like dm_tran_active_transactions Or dm_tran_database_transactions

    do not provided me the log length which I require to see which transaction is making my log size to increase.

    I have found some scripts from the internet, but need to combine them using some methods.

    that is what i am trying to resolve.

  • Here is a useful script from Paul S. Randal[/url], lists open transactions with plans and text

    😎

    SELECT

    [s_tst].[session_id],

    [s_es].[login_name] AS [Login Name],

    DB_NAME (s_tdt.database_id) AS [Database],

    [s_tdt].[database_transaction_begin_time] AS [Begin Time],

    [s_tdt].[database_transaction_log_bytes_used] AS [Log Bytes],

    [s_tdt].[database_transaction_log_bytes_reserved] AS [Log Rsvd],

    [s_est].text AS [Last T-SQL Text],

    [s_eqp].[query_plan] AS [Last Plan]

    FROM

    sys.dm_tran_database_transactions [s_tdt]

    JOIN

    sys.dm_tran_session_transactions [s_tst]

    ON

    [s_tst].[transaction_id] = [s_tdt].[transaction_id]

    JOIN

    sys.[dm_exec_sessions] [s_es]

    ON

    [s_es].[session_id] = [s_tst].[session_id]

    JOIN

    sys.dm_exec_connections [s_ec]

    ON

    [s_ec].[session_id] = [s_tst].[session_id]

    LEFT OUTER JOIN

    sys.dm_exec_requests [s_er]

    ON

    [s_er].[session_id] = [s_tst].[session_id]

    CROSS APPLY

    sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est]

    OUTER APPLY

    sys.dm_exec_query_plan ([s_er].[plan_handle]) AS [s_eqp]

    ORDER BY

    [Begin Time] ASC;

    GO

  • fn_dblog , I found useful becoz it gave me the log size created with a transaction.

    You can fire a command and check the new transaction id created in the log by this command.

    select [transaction id] ,* from fn_dblog(null,null)

    Once you get this, get an aggregated value of log size :

    select sum([log record length]),[transaction id] from fn_dblog(null,null)

    where [transaction id] = '0000:01886015' -- replace it

    group by [transaction id]

Viewing 11 posts - 1 through 10 (of 10 total)

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