Temp tables in SQL Server 2005 are not automatically deleted

Question

I am trying to eliminate an unpleasant stored procedure and noticed that after starting it, and I closed the session, there are still a lot of temporary tables in tempdb. They have names such as:

#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 this:

 use tempdb go drop TABLE #000E262B 

I get an error message:

 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 cannot 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

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

If temporary tables or table variables are often used, instead of dropping them, SQL simply "truncates" them, leaving a definition. This saves the effort of re-creating the table the next time it is needed.

Answer 2

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

How can you say that they still exist? What command do you execute to find this?

Is the connection that closed them correctly? If not, this may be the reason that they still exist.