How to drop temp table created by somebody else?

Question

Post reply

How to drop temp table created by somebody else?

JP10

SSCarpal Tunnel

Points: 4372
More actions
November 30, 2016 at 3:57 pm

#401140

I want to drop a temp table created by somebody else how would I go about doing this? I see the temp tables, but I don't know how you can drop it. I'm trying to shrink tempdb and if somebody else has a connection opened and creates a temp table I want to be able to drop that temp table. Appreciate the help.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76945 More actions · Answer 1

You can't. Temp tables are only visible to the session that created them.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

JP10 SSCarpal Tunnel Points: 4372 More actions · Answer 2

That's what I figured, but not what I want to hear. =). Ok next question is then how do we find out who the person is that created the temp table? I have seen people post traces to find, but the trace won't catch the culprit if he already created the temp table before I started the trace. Appreciate the help.

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 3

JP10 (11/30/2016)
...Ok next question is then how do we find out who the person is that created the temp table? I have seen people post traces to find, but the trace won't catch the culprit if he already created the temp table before I started the trace. Appreciate the help.

you might be able to find it by peeking at the default trace, but it doesn't always work if the table is a heap for SQL Server 2012 or later:

https://sqlperformance.com/2014/05/t-sql-queries/dude-who-owns-that-temp-table

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.SPID,

NTUserName = gt.NTDomainName + '\' + gt.NTUserName,

SQLLogin = gt.LoginName,

gt.HostName,

gt.ApplicationName,

gt.TextData -- don't bother, always NULL

FROM sys.fn_trace_gettable(@filename, DEFAULT) AS gt

INNER 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 gt.EventSubClass = 1 -- Commit

AND o.name LIKE N'#%'

JP10 SSCarpal Tunnel Points: 4372 More actions · Answer 4

Hello Chris,

I tried this before, but doesn't this trace only catch the culprit if they create the temp table at the time of the trace being ran? It won't catch the temp table creation if it was created before the trace was ran?

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 5

Well you can always look at who has open sessions on the server and close any that look like they don't belong that would remove the temp table if you kill the session that created it.

JP10 SSCarpal Tunnel Points: 4372 More actions · Answer 6

That's what I was thinking as well. I will run this query

SELECT *

FROM sys.dm_exec_cached_plans

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st

WHERE text like '%#%'

look for create table or select into statements that create a # table. Get the dbid and run sp_who2 for only that db and try to look for "ProgramName" column that has value of "Microsoft SQL Server Management Studio - Query" and one by one I guess look for any suspects with dbcc inputbuffer that has created a # table.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 7

Kill all connections. 😀

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 8

JP10 (12/1/2016)
Hello Chris,
I tried this before, but doesn't this trace only catch the culprit if they create the temp table at the time of the trace being ran? It won't catch the temp table creation if it was created before the trace was ran?

The default trace is always running, or at least it probably should be always running. check this:

select * from sys.traces where is_default = 1

JP10 SSCarpal Tunnel Points: 4372 More actions · Answer 9

Just created a tmp table and ran the query that looks at the default traces and nothing comes up for temp tables. Appreciate the help.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

JP10 (12/1/2016)
Just created a tmp table and ran the query that looks at the default traces and nothing comes up for temp tables.

Temp tables are logged in the default trace. Play with the query that you're using to filter the trace, maybe filter just on objectname like '#% to start.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

EdVassie SSC Guru Points: 60445 More actions · Answer 11

Why do you want to find out this information. What are you planning to do if you have these details?

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

JP10 SSCarpal Tunnel Points: 4372 More actions · Answer 12

Long story short is i want to be able to shrink tempdb and release the memory from disk. There are many ways to do this, but I'm looking for this particular connection that is holding the tempdb from being able to shrink...Thanks.