none
tempdb Log Filling - Cannot find how or what RRS feed

  • Question

  • Every couple weeks, the tempdb LOG fills and SQL Server is cycled to clear it out.  It happens very quickly - everything is peachy, then BAM!   Upgrading to SQL 2012 is the most significant change (I can think of) that happened before this started.

    Service Broker is used as well as CLR procedures and temp table variables.  Local temporary tables are used sparingly.

    I've read several posts and articles, but so far none seem related to this.  Service Broker endpoints are closed correctly (a few years back, we went through the problems associated with not doing this properly and fixed it).  Selecting from tempdb..sysobjects shows a bunch of temporary tables that I don't think should be there.  I assume their existence is not related to the log size.

    We do not have long running transactions (more than 100 ms) and we have a performance system on which we can run tests to ensure this.  DBCC OPENTRAN showed no open transactions when the tempdb LOG was in this state.

    With the most recent near occurrence (the log filled during the weekend, then about half the space was freed), every related service (there are about 25 of them) was shutdown one by one in an effort to see if doing so would free log space.  The 'Available Space In MB' value never significantly dropped.

    Not sure how reliable the query used to determine available space:

      Select name AS 'File Name', physical_name AS 'Physical Name', size/128 AS 'Total Size in MB'
        , size/128.0 - Cast(FileProperty(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB'
        , * 
      From sys.database_files;


    The 'Available Space In MB' was constantly changing, but there was never a significant drop. The query was taken from other posts and seems the most popular (would prefer accurate) way to check space used in devices.  

    Eighty percent of the occurrences are after our system is upgraded (we develop and sell applications with the 25+ services, and we are upgrading about once a week right now).  The upgrade sometimes requires a server reboot. The system on which this occurs is our in-house "dog food" server.

    Another of our in-house systems, which stores the main database used for testing, is in a similar state.  However, it has never experienced the tempdb LOG filling. It goes months without SQL Server being restarted.  The query indicates only 425/1,300M of the tempdb log file is available.  I would never create a tempdb more than 100M in the first place (optimist), so these values may give you an idea how wildly it has grown. 

    I'm stuck where to go next.  I'd like to know how/why our test system shows over 800M of the tempdb transaction log is used.  I doubt it is, but how would do you know?  My only guess why this happens is SQL Server gets confused with log pointers / device fragments and thinks no more space is available when space actually is available.

    The following would be helpful changes for SQL Server:
      backup/restore tempdb (so state can be captured then analyzed).
      ability to explicitly drop local temporary tables (and table variables).
         Has been 15 years, and no developer has figured out how to ensure the system drops them.
         Provide DBAs the opportunity to maintain their system.


    Tuesday, June 11, 2013 2:37 PM

All replies

  • Use this to check which SPIDS are using tempdb

    SELECT SU.session_id, 
    sum (SU.internal_objects_alloc_page_count) as session_internal_alloc,
    sum (SU.internal_objects_dealloc_page_count) as sesion_internal_dealloc, 
    sum (SU.user_objects_alloc_page_count) as session_user_alloc,
    sum (SU.user_objects_dealloc_page_count) as sesion_user_dealloc, 
    sum (TS.internal_objects_alloc_page_count) as task_internal_alloc ,
    sum (TS.internal_objects_dealloc_page_count) as task_internal_dealloc,
    sum (TS.user_objects_alloc_page_count) as task_user_alloc ,
    sum (TS.user_objects_dealloc_page_count) as task_user_dealloc
    FROM sys.dm_db_session_space_usage SU
    inner join sys.dm_db_task_space_usage TS
    on SU.session_id = TS.session_id
    where SU.session_id > 50    
    GROUP BY SU.session_id;
    

    Tuesday, June 11, 2013 2:50 PM
  • Another of our in-house systems, which stores the main database used for testing, is in a similar state.  However, it has never experienced the tempdb LOG filling. It goes months without SQL Server being restarted.  The query indicates only 425/1,300M of the tempdb log file is available.  I would never create a tempdb more than 100M in the first place (optimist), so these values may give you an idea how wildly it has grown. 

    I'm stuck where to go next.  I'd like to know how/why our test system shows over 800M of the tempdb transaction log is used.  I doubt it is, but how would do you know?  My only guess why this happens is SQL Server gets confused with log pointers / device fragments and thinks no more space is available when space actually is available.

    what makes u think that 800or 100  MB of tempdb space is enough did u do any benchmarking..There is generally only one Tempdb in sql server and you have to use it judicioulsy..I am sure ther wud be queries runnig in ur ssytem with group by and having clause they will take tempdb..its better to tune thoes queries (if required)I dont know how much is required for ur system but 800 MB is less...u first need to add space to it..monitor it for week and then revert


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Tuesday, June 11, 2013 2:58 PM
    Moderator
  • Thanks.  I ran that, but I expect many SPIDs to be using tempdb.

    Tuesday, June 11, 2013 3:40 PM
  • Thank you.

    Regardless what I expect it to be, or what is the correct size, it auto grows.  You may be confusing data space with log space, which I keep doing.

    I absolutely do not expect the log to grow more than 10 or 20M.  Although I have not done the math, the LOG gets up to 1,000M (went beyond before I limited it).  And, there are NO OPEN TRANSACTIONS.

    Tuesday, June 11, 2013 3:42 PM
  • Thanks.  

    The thing to remember is that the LOG is filling.  The log is a circular queue with constantly (hopefully) moving begin and end pointers.  The begin pointer getting stuck is the risk, right?  That happens due to an open transaction. Additional space will be allocated if the database / device has auto grow and the disk on which the device resides has space.

    Again, there are NO OPEN TRANSACTIONs.  I am emphasizing this because I keep drifting over to data space usage/issues.  But, it is the TRANSACTION LOG that is filling.

    Tuesday, June 11, 2013 4:04 PM
  • I think I was wrong - DBCC OPENTRAN does not show an open transaction.  But, the dm_tran_session_transactions shows two.

    An active transaction in Database A can result in the tempdb log filling, preventing transactions in Database B (due to implicit tempdb transactions) from running?

    Seems cloud unfriendly.

    Does anyone know if this is a behavior change in SQL 2012?

    Tuesday, June 11, 2013 6:51 PM
  • Hi

    definitely there is one or two transactions filling your log space , below query might be helpful for you

    ---Transaction causing logspace filled most
    SELECT tst.[session_id],
    s.[login_name] AS [Login Name],
    DB_NAME (tdt.database_id) AS [Database],
    tdt.[database_transaction_begin_time] AS [Begin Time],
    tdt.[database_transaction_log_record_count] AS [Log Records],
    tdt.[database_transaction_log_bytes_used] AS [Log Bytes Used],
    tdt.[database_transaction_log_bytes_reserved] AS [Log Bytes Rsvd],
    SUBSTRING(st.text, (r.statement_start_offset/2)+1, 
    ((CASE r.statement_end_offset
    WHEN -1 THEN DATALENGTH(st.text)
    ELSE r.statement_end_offset
    END - r.statement_start_offset)/2) + 1) AS statement_text,
    st.[text] AS [Last T-SQL Text],
    qp.[query_plan] AS [Last Plan]
    FROM sys.dm_tran_database_transactions tdt
    JOIN sys.dm_tran_session_transactions tst
    ON tst.[transaction_id] = tdt.[transaction_id]
    JOIN sys.[dm_exec_sessions] s
    ON s.[session_id] = tst.[session_id]
    JOIN sys.dm_exec_connections c
    ON c.[session_id] = tst.[session_id]
    LEFT OUTER JOIN sys.dm_exec_requests r
    ON r.[session_id] = tst.[session_id]
    CROSS APPLY sys.dm_exec_sql_text (c.[most_recent_sql_handle]) AS st
    OUTER APPLY sys.dm_exec_query_plan (r.[plan_handle]) AS qp
    where DB_NAME (tdt.database_id) = 'tempdb'
    ORDER BY [Log Bytes Used] DESC;


    Thanks Saurabh Sinha

    http://saurabhsinhainblogs.blogspot.in/

    Please click the Mark as answer button and vote as helpful if this reply solves your problem




    Tuesday, June 11, 2013 6:54 PM
  • Select name AS 'File Name', physical_name AS 'Physical Name', size/128 AS 'Total Size in MB'
    	, size/128.0 - Cast(FileProperty(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB'
    	, * 
    From sys.database_files;


    The above query is currently showing 1310 for 'Total Size in MB' and 453 for 'Available Space in MB' when run from tempb.

    Select *
    From sys.dm_tran_session_transactions;

    That query returns no rows.

    So, why is tempdb log space not freed?



    • Edited by DBAatNEC Thursday, June 13, 2013 2:05 PM
    Thursday, June 13, 2013 2:05 PM
  • Sys.dm_tran_database_transactions give active transaction or praticular DB ..did u ran it into temp db..

    (Srry talking abt databse transaction)

    The tempdb system database is a global resource that is available to all users that are connected to an instance of SQL Server. The tempdb database is used to store the following objects: user objects, internal objects, and version stores..so its showing some space used..

    Queries available in below link will help u get usage details for all 

    http://msdn.microsoft.com/en-us/library/ms176029(v=sql.105).aspx


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers



    Thursday, June 13, 2013 2:56 PM
    Moderator
  • Yes, the query was run in tempdb, but I do not think the database context matters.

    After running DBCC SHRINKDATABASE( tempdb ), the reported tempdb transaction log space usage went to zero.  IOW, the values reported by the query were incorrect.

    Thursday, June 13, 2013 7:11 PM