How to drop #Temp_______hex tables?

Question

Our Temp DB has grown very large and we'd like to clean it up.

When i open the tempdb, temptables folder, I notice a like of tables named like so:

#Somename________hexcode
#Someothername________hexcode

etc...

we know we can delete them because those aren't being used. When I query the sysobjects they are listed. But when I try to drop them, it says they don't exist.

Any ideas on how to remove those would be welcome. Thank you.

Don't use sysobjects unless you are on SQL Server 2000. Use sys.objects. — Aaron Bertrand, Sep 11 '13 at 16:21

score 1 · Answer 1 · answered Sep 11 '13 at 16:20

You can't drop #temp tables created by someone else's session. You can clean up tempdb by cycling the SQL Server service or waiting for those sessions to go out of scope so the tables can be destroyed (there's a background process for doing this). It may not be happening if you're hitting an obscure bug in certain versions with the Temp Tables for Destruction counter.

How to drop #Temp_______hex tables?

1 Answers1