How to cleanup TempDB Temporary Tables?

• David Bird

  SSCarpal Tunnel

  Points: 4669

  More actions

  August 7, 2007 at 10:07 am

  #373137

  I noticed using SQL Server Management Studios the system database TEMPDB has a folder Temporary Tables. It list a bunch of tables starting with # followed by hex values.

  For example a dbo.#02CF837D

  If I run a query against sys.objects it identifies these as User Tables. I would understand them existing on servers with connections but I found them on a server without any connections. I've tried dropping them but it does not work.

  The only way I get rid of them is to restart the instance.

  Is there a way to clean them up without restarting the instance?

  I am running SQL Server 2005.3159

  David Bird

• Yelena Varshal

  SSC-Dedicated

  Points: 34286

  More actions

  August 7, 2007 at 1:17 pm

  #725264

  You probably should not touch these tables. These are local tables (with one #) and can be accesses only by the creating connection. These tables have nothing to do with the explicitly created local temp tables, in this case they would be named like #TempTableName____________________000001234 with having this long line in the name ending with numbers. On my computer these tables are created by the backup job using Maintenance Plan. I restarted the server, these tables disappeared. I did several actions, no temp tables. Then I ran the backup job for the Maintenance Plan and 5 tables as you desribed appeared.

  Regards,Yelena Varsha