Mapping temp tables to users

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 DISTINCT
o.name,
o.OBJECT_ID,
o.create_date,
gt.NTUserName,
gt.HostName,
gt.SPID, (8*p.reserved_page_count)/1024.0 size_mb
FROM sys.fn_trace_gettable( @FileName, DEFAULT ) AS gt
JOIN tempdb.sys.objects AS o WITH (NOLOCK) ON gt.ObjectID = o.OBJECT_ID
INNER JOIN tempdb.sys.dm_db_partition_stats p WITH (NOLOCK) ON p.object_id = o.object_id AND p.index_id IN (0,1)
WHERE gt.DatabaseID = 2

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s


%d bloggers like this: