Sql-server – Find which session is holding which temporary table

sql serversql-server-2005tempdb

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,

Best Answer

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, but that search is 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:

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

Related Solutions

Sql-server – How to migrate SQL Server Stored Procedures using temporary tables or table variables to Oracle

One way to do this would be Object Types, in this case the type would be analagous to your #t1. So it would need to be defined somewhere but it would not need to be global, it could be per-schema or per-procedure even. First, we can create a type:

SQL> create or replace type t1_type as object (x int, y int, z int)
  2  /

Type created.

SQL> create or replace type t1 as table of t1_type
  2  /

Type created.

Now set up some sample data:

SQL> create table xy (x int, y int)
  2  /

Table created.

SQL> insert into xy values (1, 2)
  2  /

1 row created.

SQL> insert into xy values (3, 4)
  2  /

1 row created.

SQL> commit
  2  /

Commit complete.

And create a function over this data returning our "temporary" type:

SQL> create or replace function fn_t1 return t1 as
  2  v_t1 t1 := t1();       -- empty temporary table (really an array)
  3  v_ix number default 0; -- array index
  4  begin
  5  for r in (select * from xy) loop
  6  v_ix := v_ix + 1;
  7  v_t1.extend;
  8  v_t1(v_ix) := t1_type(r.x, r.y, (r.x + r.y));
  9  end loop;
 10  return v_t1;
 11  end;
 12  /

Function created.

And finally:

SQL> select * from the (select cast (fn_t1 as t1) from dual)
  2  /

         X          Y          Z
---------- ---------- ----------
         1          2          3
         3          4          7

As you can see this is pretty clunky (and uses collection pseudo-functions, which is an obscure feature at the best of times!), as I always say, porting from DB to DB is not merely about syntax and keywords in their SQL dialects, the real difficulty comes in different underlying assumptions (in the case of SQL Server, that cursors are expensive and their use avoided/worked around at all costs).

Find Transactions Filling Up the Version Store in SQL Server

It doesn't really make sense to track version store by session, or by transaction, or by query. If two different users are making use of the same version of a row/table, who owns it?

You can track this by object, though, which can help you narrow down which modules are causing the churn. Have a look at sys.dm_tran_top_version_generators:

USE [your database];
GO
SELECT obj = 
  QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id))
  + '.' + QUOTENAME(OBJECT_NAME(p.object_id)),
  vs.aggregated_record_length_in_bytes
FROM sys.dm_tran_top_version_generators AS vs
INNER JOIN sys.partitions AS p
ON vs.rowset_id = p.hobt_id
WHERE vs.database_id = DB_ID()
AND p.index_id IN (0,1);

And on SQL Server 2008+, you can also figure out which modules reference these tables by adding sys.dm_sql_referencing_entities:

SELECT 
  obj = QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id))
  + '.' + QUOTENAME(OBJECT_NAME(p.object_id)),
  referenced_by = QUOTENAME(r.referencing_schema_name)
  + '.' + QUOTENAME(r.referencing_entity_name),
  vs.aggregated_record_length_in_bytes AS size
FROM sys.dm_tran_top_version_generators AS vs
INNER JOIN sys.partitions AS p
ON vs.rowset_id = p.hobt_id
CROSS APPLY sys.dm_sql_referencing_entities
(
  QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id))
  + '.' + QUOTENAME(OBJECT_NAME(p.object_id)), 'OBJECT'
) AS r
WHERE vs.database_id = DB_ID()
AND p.index_id IN (0,1)
ORDER BY size DESC, referenced_by;

This assumes that none of the version store could be created by ad hoc queries. However, it doesn't tell you which of those modules could be causing it - hopefully the naming scheme is logical and helps you narrow it down a bit.

(On 2005 you might be able to go through sysdepends and other old-style dependency views but I'm not 100% sure how reliable that would be.)

Best Answer

Related Solutions

Sql-server – How to migrate SQL Server Stored Procedures using temporary tables or table variables to Oracle

Find Transactions Filling Up the Version Store in SQL Server

Related Question