Another use for the Default Trace: Mapping Temporary Tables to Sessions

http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/09/29/what-session-created-that-object-in-tempdb.aspx

The random ramblings and rantings of frazzled SQL Server DBA

Another use for the Default Trace: Mapping Temporary Tables to Sessions

Here’s another use for the information captured by the default trace.  Ever wonder what session created the tables that exist in tempdb?  Well you can find it using the default trace and the sys.objects view in tempdb.  Any time an object is created in a database, EventClass 46 (Object:Created) is captured in the trace output of the DMV.  By scanning the trace files for a matching ObjectID that was created 100 ms before or after the objects create_date with DatabaseID = 2 (tempdb) you can match the objects back to the SPID (session_id) that created the object:

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. TEXT Data 
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 )

If you’ve seen my presentation for the PASS DBA SIG (no longer a SIG but a Virtual Chapter), you will notice a difference in how the @Filename variable is being built in the above code.  Previously I used the fn_trace_getinfo function to get the filename and did a long string parse to go back four file numbers to the current start file.  This is not necessary however if you just use Log.trc as the filename since it will automatically roll forward through the existing files from this point which makes for cleaner code in my opinion.

If I have missed something and there is a better way to do this using DMV’s please post a comment and let me know.

 

 

Additional Information: from envykok

 

--Get ALL Temp Table Size

USE [Tempdb]

GO

 SELECT CAST(sysobjects.[name] AS VARCHAR) AS [TABLE_NAME],
       sysindexes.[rows] AS [NO_OF_ROWS],
       sysindexes.reserved AS [RESERVED_SPACE],
       sysindexes.used AS [USED_SPACE]
  FROM sysobjects
 INNER JOIN sysindexes
    ON sysobjects.[id] = sysindexes.[id]
 WHERE sysindexes.indid < 2
   AND sysobjects.type = 'U'