February 20, 2009 at 9:25 am
Please do not ask me why I do not use a local temporary table. It is just the life when a DBA works as a data (furniture) mover. :crying:
Someone created a global temporary table. Then, ask our DBAs to drop it. Apparently, we could not unless restarting the database engine. It is not realistic. Based on BOL, a global temporary table will go away after the last connection is off. How do we determine who hold connections to a global temporary table?
Any input will be greatly appreciated.
February 20, 2009 at 9:43 am
In Management Studio, you should be able to pop open the Activity Monitor, and see what connections are active and all that. That might be a good place to start.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 20, 2009 at 9:43 am
I may be misunderstanding the question but if you are trying to get rid of a global temp table that should be as easy as find the table name
(select * from tempdb.sys.objects where type = 'U' and name like '##%')
and then dropping the table.
If it is a regular temp table, then this should not be a problem anyway. But you can look at the sys.objects in tempdb the same way you can in other db's.
February 20, 2009 at 9:49 am
Thank you so much for the input above.
Unfortunately, a temporary table can only be dropped from the connection session where it was created.
From the Activity Monitor, I cannot determine which one has a connection to the temporary table. If you know, would you please show me in detail?
Many thanks once again.
February 20, 2009 at 9:54 am
A global (##) temp table can be dropped from any connection (not just the one that created it). A regular (#) temp table is tied to the connection that created it.
If you are dealing with a regular (#) there really should not be an issue, since you can have as many of the same name ones as there are connections to the server. There really should not be a need to drop one of these anyway that I can think of, unless I am misunderstanding the question.
In terms of trying to figure out what connection created either of these tables, I have no clue. Someone else may have some knowledge on that but it is beyond me.
February 20, 2009 at 10:21 am
You can determine if someone has a transaction running on tempdb with DBCC OPENTRAN('tempdb')
* Noel
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply