April 21, 2008 at 10:16 pm
Hi,
I check my temporary tables folders under tempdb in the sql server 2005 management studio and found that there are many temp tables not get dropped,some of them are there for few months. how can i drop them?
many thanks
April 22, 2008 at 12:06 am
[font="Verdana"]
The same way you drop table. i.e. Drop Table {Temp Table}
You must have forgotten to drop then. Make a habit tp Drop such table after their use. Also check whether they are exists before creating them.
Mahesh[/font]
MH-09-AM-8694
April 22, 2008 at 7:57 am
If I'm not mistaken, #temp table are implicitly dropped at the end of the stored procedure regardless of whether or not you explicitly drop it. ##temp tables (global ones) must be explicitly dropped.
You could always reboot SQL Server, because that deletes the tempdb and rebuilds it on SQL Server start.
April 22, 2008 at 3:20 pm
Are you creating tables directly in TempDB?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 22, 2008 at 10:48 pm
[font="Verdana"]
Jeremy (4/22/2008)
If I'm not mistaken, #temp table are implicitly dropped at the end of the stored procedure regardless of whether or not you explicitly drop it. ##temp tables (global ones) must be explicitly dropped.You could always reboot SQL Server, because that deletes the tempdb and rebuilds it on SQL Server start.
You are absolutely right Jeremy. From BOL:
Local Temporary Table
Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped using DROP TABLE
Even Global Temporary Table get auto dropped... From BOL:
Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them.
But I think Precaution is better than cure. 😉 why to wait till they get auto dropped?
Mahesh
[/font]
MH-09-AM-8694
April 22, 2008 at 11:56 pm
Mahesh Bote (4/22/2008)
[font="Verdana"]Jeremy (4/22/2008)
If I'm not mistaken, #temp table are implicitly dropped at the end of the stored procedure regardless of whether or not you explicitly drop it. ##temp tables (global ones) must be explicitly dropped.You could always reboot SQL Server, because that deletes the tempdb and rebuilds it on SQL Server start.
You are absolutely right Jeremy. From BOL:
Local Temporary Table
Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped using DROP TABLE
Even Global Temporary Table get auto dropped... From BOL:
Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them.
But I think Precaution is better than cure. 😉 why to wait till they get auto dropped?
Mahesh
[/font]
No cure needed... they automatically drop at the end of the proc (poet and don't know it, too!) 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
April 23, 2008 at 12:19 am
[font="Verdana"]
No cure needed... they automatically drop at the end of the proc (poet and don't know it, too!)
Thanks Jeff for updating me. I was under impression that, they get dropped auto but not immediately. That’s why I was recommending dropping them at the end of Store Procedure. Anyways, I will remove the Drop Table statements from Store Procedure 🙂 wherever I have used them.
Mahesh
[/font]
MH-09-AM-8694
April 23, 2008 at 6:53 am
Mahesh Bote (4/23/2008)
[font="Verdana"]No cure needed... they automatically drop at the end of the proc (poet and don't know it, too!)
Thanks Jeff for updating me. I was under impression that, they get dropped auto but not immediately. That’s why I was recommending dropping them at the end of Store Procedure. Anyways, I will remove the Drop Table statements from Store Procedure 🙂 wherever I have used them.
Mahesh
[/font]
Sounds contradictory on my part, but you don't have to remove the DROP statements... they cost only a clock cycle or two and if they make folks more comfortable or serve as a reminder to others that the temp table won't exist after the proc runs, then leave them in.
My only point was that they don't need to be explicity dropped.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 23, 2008 at 7:17 am
Jeremy (4/22/2008)
If I'm not mistaken, #temp table are implicitly dropped at the end of the stored procedure regardless of whether or not you explicitly drop it. ##temp tables (global ones) must be explicitly dropped.You could always reboot SQL Server, because that deletes the tempdb and rebuilds it on SQL Server start.
Keep in mind that temp tables get dropped when the batch operation goes "out of scope". In the case of a stored proc, that would be when it finishes executing. In the case of ad-hoc queries, that would mean when the connection is severed/dropped/closed. This is where the garbage collection might be an issue if you're using connection pooling, since connections will look (to SQL server) like they're still open long after the actual user connection is gone.
so - depending on your code, and what happens to the connection - you might HAVE to drop the tables. Classic ASP with conn pooling was particularly bad for this....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 23, 2008 at 12:48 pm
You should be aware that there are applications that create "permanent" tables in the tempdb (no # or ##). They are "permanent" until the next time the service is restarted. You probably won't lose any critical data by dropping them, but you might want to find out how and why the tables were created before you drop them.
April 24, 2008 at 1:14 pm
Matt,
Are you sure this is the case? I just created a proc which creates a temp table called "#temp" and ran the proc through SQL Management Studio. After the proc completed, I opened the tempdb using SQL 2000 Query Analyzer and saw a temp table called "dbo.#4F7CD00D" that contained my columns.
Most of our procs which create temp tables have drop statements at the end, but this drop statement seems to cause a bunch of problems for some tests with Reporting Services These procs are currently used in a web based investment reporting system and the temp tables may sometimes have up to several millions rows. If we remove the drop statement from the procs, I gather we might start running into problems with the tempdb building up in size until either the web app drops its db connection or the SQL Server is restarted.
Any comments?
Erik
April 24, 2008 at 1:34 pm
I believe that the temp table will stay there as long as you have the SSMS window open, which is why when developing, I always put the drops in so I can run the same proc over and over again in the same window without worrying. I'll bet if you run the proc, and then close your window, you won't see it...
April 24, 2008 at 1:45 pm
Jeremy,
I closed the window that ran the proc (spid 70) as well as the whole Management Studio app but the temp table was still there after refreshing the list of tables in the tempdb.
I also confirmed that spid 70 was no longer running by running sp_who2. The temp table was still there even after closing Query Analyzer and then going back into it.
This SQL Server happens to be on my local XP box running SQL 2005 SP2 with cumulative update 7.
Erik
April 24, 2008 at 3:54 pm
Erik (4/24/2008)
Matt,Are you sure this is the case? I just created a proc which creates a temp table called "#temp" and ran the proc through SQL Management Studio. After the proc completed, I opened the tempdb using SQL 2000 Query Analyzer and saw a temp table called "dbo.#4F7CD00D" that contained my columns.
Most of our procs which create temp tables have drop statements at the end, but this drop statement seems to cause a bunch of problems for some tests with Reporting Services These procs are currently used in a web based investment reporting system and the temp tables may sometimes have up to several millions rows. If we remove the drop statement from the procs, I gather we might start running into problems with the tempdb building up in size until either the web app drops its db connection or the SQL Server is restarted.
Any comments?
Erik
Can't say I've tried that particular testing routine as you are describing, but I'm pretty sure that referring to it by its original name (#temp) fails as soon as the SP is done executing. Now - it may be that it's just slow to get around to actually killing off the table, so disabling the name right away was how they were planning on getting around that....
I'll have to see if i can replicate or not...
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply