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.

We have a SQL Server 2005 database the temp database has become full. By going into SQL Server Management Studio I can see all the temporary tables in the tempdb. Is it possible to tell which session is holding which temp table? Ideally a query which would list temp tables used by each session.

Thanks,

share|improve this question
2  
Why are you concerned about pulling information based on particular and specific temp tables? They are transient by nature. What exactly are you trying to find? Perhaps sessions that have consumed the most tempdb and their last run query? Maybe current tasks' consumption of tempdb? And, of course, the query text that goes along with those. Those are typical requirements. Or am I missing exactly what you're trying to get at? –  Thomas Stringer Mar 25 at 15:42
    
Thanks for your comments. We would like to track down the specific user who is using the space in the temp database. It is the current tasks' consumption of tempdb we are interested in. –  SQLMIKE Mar 25 at 16:33
    
See my answer below. That query should give you the top current tempdb page allocation consumers. –  Thomas Stringer Mar 25 at 16:57
    
    
...and I have since blogged about it. –  Aaron Bertrand May 21 at 16:49

2 Answers 2

I asked for something built in back in 2007, but this was rejected for the 2008 release, and subsequently ignored. Feel free to vote and, more importantly, comment about your business need.

In the meantime, for SQL Server 2005 and 2008, you should be able to pull this information from the default trace:

DECLARE @FileName VARCHAR(MAX)  

SELECT @FileName = SUBSTRING(path, 0,
   LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc'  
FROM sys.traces   
WHERE is_default = 1;  

SELECT   
     o.name,   
     o.OBJECT_ID,  
     o.create_date, 
     gt.NTUserName,  
     gt.HostName,  
     gt.SPID,  
     gt.DatabaseName,  
     gt.TEXTData 
FROM sys.fn_trace_gettable( @FileName, DEFAULT ) AS gt  
JOIN tempdb.sys.objects AS o   
     ON gt.ObjectID = o.OBJECT_ID  
WHERE gt.DatabaseID = 2 
  AND gt.EventClass = 46 -- (Object:Created Event from sys.trace_events)  
  AND o.create_date >= DATEADD(ms, -100, gt.StartTime)   
  AND o.create_date <= DATEADD(ms, 100, gt.StartTime)

Shamelessly lifted from this Jonathan Kehayias blog post.

To determine space usage you could further enhance this to join in data from views like sys.db_db_partition_stats - e.g.:

DECLARE @FileName VARCHAR(MAX)  

SELECT @FileName = SUBSTRING(path, 0,
   LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc'  
FROM sys.traces   
WHERE is_default = 1;  

SELECT   
     o.name,   
     o.OBJECT_ID,  
     o.create_date, 
     gt.NTUserName,  
     gt.HostName,  
     gt.SPID,  
     gt.DatabaseName,  
     gt.TEXTData,
     row_count = x.rc,
     used_page_count = x.upc
FROM sys.fn_trace_gettable( @FileName, DEFAULT ) AS gt  
JOIN tempdb.sys.objects AS o   
     ON gt.ObjectID = o.OBJECT_ID
INNER JOIN
(
 SELECT [object_id], SUM(row_count), SUM(used_page_count)
   FROM tempdb.sys.dm_db_partition_stats
   WHERE index_id IN (0,1)
   GROUP BY [object_id]
) AS x(id, rc, upc)
ON x.id = o.[object_id]
WHERE gt.DatabaseID = 2 
  AND gt.EventClass = 46 -- (Object:Created Event from sys.trace_events)  
  AND o.create_date >= DATEADD(ms, -100, gt.StartTime)   
  AND o.create_date <= DATEADD(ms, 100, gt.StartTime)

The problem here is trying to correlate a table name by query text; this just isn't practical, since most of the time, the user isn't still executing a query against that table (never mind still running the one that created / populated it).

However, and this is for other readers (or for you when you upgrade), the default trace in 2012+ no longer tracks temp table object creation, if the #temp table is a heap. Not sure if that is a coincidence or directly related to the fact that starting in 2012 all temp tables now have a negative object_id. You could of course move to Extended Events to help you collect and track this information, but that is possibly a lot of manual work (and I've only verified that this is no longer tracked in trace - you may not be able to pick it up in Extended Events either). The default trace will pick up #temp tables created with a PK or other constraint, or with constraints or indexes added after the creation event, but then you'll have to loosen up the time-based restrictions above (an index can be created much later than 100ms after creation).

Some other answers on this site that may be useful:

I've also blogged about this, with a custom Extended Events session to track this information in SQL Server 2012 and up:

And Paul White has blogged about reading pages directly (not exactly for the faint of heart, nor easy to automate in any way):

share|improve this answer

Here's a query that should get you started on finding out the information you're looking for:

select top 10
    tsu.session_id,
    tsu.request_id,
    r.command,
    s.login_name,
    s.host_name,
    s.program_name,
    total_objects_alloc_page_count = 
        tsu.user_objects_alloc_page_count + tsu.internal_objects_alloc_page_count,
    tsu.user_objects_alloc_page_count,
    tsu.user_objects_dealloc_page_count,
    tsu.internal_objects_alloc_page_count,
    tsu.internal_objects_dealloc_page_count,
    st.text
from sys.dm_db_task_space_usage tsu
inner join sys.dm_exec_requests r
on tsu.session_id = r.session_id
and tsu.request_id = r.request_id
inner join sys.dm_exec_sessions s
on r.session_id = s.session_id
outer apply sys.dm_exec_sql_text(r.sql_handle) st
where tsu.user_objects_alloc_page_count > 0
or tsu.internal_objects_alloc_page_count > 0
order by total_objects_alloc_page_count desc;

This query pulls useful information for the top 10 tasks, such as pages allocated/deallocated, the SQL text of the tasks (if available), etc.

These DMVs are full of great information, so if you require more data then you can mix and match with what you are pulling. But this should be a starting point for troubleshooting current tempdb consumption tasks.

share|improve this answer
    
Thanks that looks pretty good. The odd thing is if I run a 'Disk Usage by Top Tables' report on the tempdb the table which is using the most space doesn't appear in the st.text. The table is still there after I run the query. –  SQLMIKE Mar 25 at 17:06
1  
@SQLMIKE if you just want to know which table is the biggest, you can get that from tempdb.sys.dm_db_partition_stats. Unfortunately you can't really tell which copy of #some_table_name belongs to which user, nor are you always going to be able to pull statement text that references that table at any given time - that may not be the query that user is currently running. You might want to see this and this –  Aaron Bertrand Mar 25 at 17:10

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.