Find which session is holding which temporary table

Question

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,

Answer 1

I asked for something to be built in back in 2007, on Connect. This was rejected for the 2008 release, and subsequently ignored, until Connect died a few years ago. I tried to find it on the new feedback site for SQL Server](which has also been killed, but that search was an absolute dumpster fire. The title of my request was "dmv to map temp table to session_id" - since the search can only do OR, "map temp table" returns 118 pages of results. Google seems to suggest the item didn't make the cut when they killed Connect.

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:

• How to identify which query is filling up the tempdb transaction log?

• Issues with TempDB mdf file ever increasing

• Find transactions that are filling up the version store

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

• Dude, who owns that #temp table?

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

• Viewing Another Session’s Temporary Table

Answer 2

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.