Getting rid of #temp tables without restarting SQL Service

If you create a #temp table on stored procedure #1

and then call another stored procedure #2 from #1; if stored procedure #2 has an error in it, the #temp table will not release even though stored procedure #1 has a drop table statement in it.

Hi Steve,

Can you elaborate on what you mean by "release"? The temporary table should exist for the context of Procedure #1 until Procedure #1 is finished executing. The only time you might see the temp table is if you accidentally created it before executing procedure 1.

You could include code in procedure #1 to drop the temporary table if it exists at the beginning of the procedure:

IF OBJECT_ID('TEMPDB..#Test') IS NOT NULL

BEGIN

PRINT 'Dropping #Test'

DROP TABLE #TEST

END

CREATE TABLE #Test (Pkey INT PRIMARY KEY)

One thing you should be aware of, though - A temporary table that's used in a procedure that did not create it can lead to recompiles in your procedure. Depending on the complexity of your query, this can cause performance problems as it must determine the execution plan each time Procedure #2 is executed. If you have the option, you should turn procedure #2 into a table function or move the logic into procedure #1.

|||

Jared,
What I mean by release is; if Procedure #1 (this Procedure creates temporary table) calls Procedure #2 and Procedure #2 has an error in it, the temporary table will not be dropped, even with a drop statement. The only way to get rid of the temp table is to restart the SQL Service or reboot.

|||

Could you please post an example of the SPs (not your actual code)? What is the error message you are getting? This is not possible since the temporary table(s) (#something type of tables) created within SP#1 will get dropped once the execution stops either successfully or prematurely. Are you sure it is temporary tables with #something and not ##something? The latter are global temporary tables so they persist for the connection duration or until the last consumer of the temporary table. If you are using temporary tables (#something) and you are seeing orphans like you described then it is a bug. You can report the bug with a complete repro using http://connect.microsoft.com/feedback/default.aspx?SiteID=68.