Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I would like to know how to identify the exact query or stored proc which is actually filling up the transactional log of TEMPDB database.

share|improve this question

migrated from stackoverflow.com Jun 25 '12 at 15:28

This question came from our site for professional and enthusiast programmers.

1  
See Troubleshooting Insufficient Disk Space in tempdb on MSDN. –  David Brabant Jun 25 '12 at 14:55
    
I am new to this site and not sure how to edit the post. I don't have access to PROD to give more info. All i am hearing from PROD DBA are your code is filling up the tempdb! Is there any coding best practices to be followed in order to make sure our code doesn't fill up the log of tempdb? –  prasanth Jun 25 '12 at 15:14
    
@prasanth You will need to sign up for this site with your same openid to make alterations to your question here. It depends on what your code is doing as to why it is using tempdb. The execution plan should show what it is doing, and if you post the actual code, we can help improve it. –  Cade Roux Jun 25 '12 at 16:27
    
@CadeRoux I think he's trying to identify the query (or queries), not trying to figure out why a specific, known query is causing the problem. –  Aaron Bertrand Jun 25 '12 at 16:31
    
@AaronBertrand yeah, but the comment seems to indicate he wants best practices for coding. –  Cade Roux Jun 25 '12 at 17:17
show 1 more comment

1 Answer 1

up vote 22 down vote accepted

From http://www.sqlservercentral.com/scripts/tempdb/72007/

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

EDIT

As Martin pointed out in a comment, this would not find active transactions that are occupying space in tempdb, it will only find active queries that are currently utilizing space there (and likely culprits for current log usage). You could change the inner join on sys.dm_exec_requests to a left outer join, then you will return rows for sessions that aren't currently actively running queries.

The query Martin posted...

SELECT database_transaction_log_bytes_reserved,session_id 
  FROM sys.dm_tran_database_transactions AS tdt 
  INNER JOIN sys.dm_tran_session_transactions AS tst 
  ON tdt.transaction_id = tst.transaction_id 
  WHERE database_id = 2;

...would identify session_ids with active transactions that are occupying log space, but you wouldn't necessarily be able to determine the actual query that caused the problem, since if it's not running now it won't be captured in the above query for active requests. You may be able to reactively check the most recent query using DBCC INPUTBUFFER but it may not tell you what you want to hear. You can outer join in a similar way to capture those actively running, e.g.:

SELECT tdt.database_transaction_log_bytes_reserved,tst.session_id,
       t.[text], [statement] = COALESCE(NULLIF(
         SUBSTRING(
           t.[text],
           r.statement_start_offset / 2,
           CASE WHEN r.statement_end_offset < r.statement_start_offset
             THEN 0
             ELSE( r.statement_end_offset - r.statement_start_offset ) / 2 END
         ), ''
       ), t.[text])
     FROM sys.dm_tran_database_transactions AS tdt
     INNER JOIN sys.dm_tran_session_transactions AS tst
     ON tdt.transaction_id = tst.transaction_id
         LEFT OUTER JOIN sys.dm_exec_requests AS r
         ON tst.session_id = r.session_id
         OUTER APPLY sys.dm_exec_sql_text(r.plan_handle) AS t
     WHERE tdt.database_id = 2;

You can also use the DMV sys.dm_db_session_space_usage to see overall space utilization by session (but again you may not get back valid results for the query; if the query is not active, what you get back may not be the actual culprit).

;WITH s AS
(
    SELECT 
        s.session_id,
        [pages] = SUM(s.user_objects_alloc_page_count 
          + s.internal_objects_alloc_page_count) 
    FROM sys.dm_db_session_space_usage AS s
    GROUP BY s.session_id
    HAVING SUM(s.user_objects_alloc_page_count 
      + s.internal_objects_alloc_page_count) > 0
)
SELECT s.session_id, s.[pages], t.[text], 
  [statement] = COALESCE(NULLIF(
    SUBSTRING(
        t.[text], 
        r.statement_start_offset / 2, 
        CASE WHEN r.statement_end_offset < r.statement_start_offset 
        THEN 0 
        ELSE( r.statement_end_offset - r.statement_start_offset ) / 2 END
      ), ''
    ), t.[text])
FROM s
LEFT OUTER JOIN 
sys.dm_exec_requests AS r
ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.plan_handle) AS t
ORDER BY s.[pages] DESC;

With all of these queries at your disposal, you should be able to narrow down who is using up tempdb and how, especially if you catch them in the act.

some tips for minimizing tempdb utilization

  1. use fewer #temp tables and @table variables
  2. minimize concurrent index maintenance, and avoid the SORT_IN_TEMPDB option if it isn't needed
  3. avoid unnecessary cursors; avoid static cursors if you think this may be a bottleneck, since static cursors use work tables in tempdb - though this is the type of cursor I always recommend if tempdb isn't a bottleneck
  4. try to avoid spools (e.g. large CTEs that are referenced multiple times in the query)
  5. don't use MARS
  6. thoroughly test the use of snapshot / RCSI isolation levels - don't just turn it on for all databases since you've been told it's better than NOLOCK (it is, but it isn't free)
  7. in some cases, it may sound unintuitive, but use more temp tables. e.g. breaking up a humongous query into parts may be slightly less efficient, but if it can avoid a huge memory spill to tempdb because the single, larger query requires a memory grant too large...
  8. avoid enabling triggers for bulk operations
  9. avoid overuse of LOB types (max types, XML, etc) as local variables
  10. keep transactions short and sweet
  11. don't set tempdb to be everyone's default database -

You may also consider that your tempdb log usage may be caused by internal processes that you have little or no control over - for example database mail, event notifications, query notifications and service broker all use tempdb in some way. You can stop using these features, but if you're using them you can't dictate how and when they use tempdb.

share|improve this answer
    
Thanks for the link Aaron. In general is there any coding best practices which needs to be followed to avoid filling up of TEMPDB Transactional logs? –  prasanth Jun 25 '12 at 15:15
    
Hmm, Just tested that and it hasn't found my offending session even though the session_id shows up with the following query SELECT database_transaction_log_bytes_reserved,session_id FROM sys.dm_tran_database_transactions tdt JOIN sys.dm_tran_session_transactions tst ON tdt.transaction_id = tst.transaction_id WHERE database_id = 2. The query I was expecting to find was after running the following BEGIN TRAN CREATE TABLE #T(X CHAR(8000)) INSERT INTO #T SELECT name FROM sys.objects –  Martin Smith Jun 25 '12 at 15:19
    
@Martin: Noticed there's an @@SPID in the cte, which would limit the results to the current session. If you want it to span across all sessions, remove that. –  Ben Thul Jun 25 '12 at 15:25
    
@BenThul - I ran the query in another connection. The @@SPID is <> not =. dm_db_task_space_usage reports 0 for the spid with the open transaction for all columns for me. Wonder if you need to query it when the request is actually executing rather than idle with an open transaction. –  Martin Smith Jun 25 '12 at 15:26
    
@MartinSmith the query only finds active requests, not active transactions. So if the query is no longer running, you're right, you could track back using the transaction DMVs. But you wouldn't necessarily be able to figure out the query that caused it if it's no longer running - that same spid might have issued several other statements in the current transaction. –  Aaron Bertrand Jun 25 '12 at 15:29
show 3 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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