Temp tables in SQL Server 2005 not automatically dropped

Question

Hi there,

I'm troubleshooting a nasty stored procedure and noticed that after running it, and I have closed my session, lots of temp tables are still left in tempdb. They have names like the following:

#000E262B
#002334C4
#004E1D4D
#00583EEE
#00783A7F
#00832777
#00CD403A
#00E24ED3
#00F75D6C

If I run this code:

if object_id('tempdb..#000E262B') is null   
print 'Does NOT exist!'

I get:

Does NOT exist!

If I do:

use tempdb
go
drop TABLE #000E262B

I get an error:

Msg 3701, Level 11, State 5, Line 1
Cannot drop the table '#000E262B', because it does not exist or you do not have permission.

I am connected to SQL Server as sysadmin. Using SP3 64-bit. I currently have over 1100 of these tables in tempdb, and I can't get rid of them. There are no other users on the database server.

Stopping and starting SQL Server is not an option in my case.

Thanks!

Answer 1

Tables created with the # prefix are only available to the current connection. Therefore any new connection you create will not be able to see them and therefore not be able to drop them.

How can you tell that they still exist? What command are you running to find this out?

Is the connection that created them closed properly? If not then this may be why they still exist.

Answer 2

http://www.sqlservercentral.com/Forums/Topic456599-149-1.aspx

If temp tables or table variables are frequently used then, instead of dropping them, SQL just 'truncates' them, leaving the definition. It saves the effort of recreating the table next time it's needed.