THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Rambling DBA: Jonathan Kehayias

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

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.

Published Tuesday, September 29, 2009 6:06 AM by Jonathan Kehayias

Comments

 

Adam Machanic said:

Great work! This is definitely going to come in handy.

September 29, 2009 11:32 AM
 

Miller said:

very very very very very very GOOD!

great article.

July 12, 2010 4:15 PM
 

Samson J, Loo said:

Man this is awesome. Thanks for sharing this.

January 29, 2011 5:52 PM
Anonymous comments are disabled

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement