drop orphan temporary tables in sql server 2005

How do I dump orphaned temporary tables in sql server 2005?  I have to restart my sql server every week or so because my tempdb is filling up with strange little temporary tables with names like:


I am guessing these were orphaned tables from a session that exited ungracefully.  However, I always name my temp tables in the procs that I run so I'm not sure why they don't have names like


I'm guessing the former were created via a temp table variable like so

declare @mytemp table (myfield varchar(2))

I think that's how its done.  In any event, whatever these orphans are, if they are orphans, then I want to nuke them.
Who is Participating?
Scott PletcherConnect With a Mentor Senior DBACommented:
SQL itself creates and uses temp tables for various things, including to satisfy system commands and for sorting result sets.

Have you checked the MS Knowledge Base to see if there is a known issue about "left-over" temp tables?  Otherwise, they may be active and you don't know it.

You can always explicitly DROP the table(s) yourself.  For example, this will generate commands you can run:

FROM tempdb.dbo.sysobjects
WHERE name LIKE N'#[0123456789]%'

However, I would be **very careful** about doing this -- again, SQL itself uses temp tables for internal processes.

Also, exactly how large is the tempdb?
How much RAM does SQL have?
tomandlisAuthor Commented:
btw, while googling about I found a command called DBCC ORPHANTABLES, but that is for Sybase and apparently didn't make it into SQL Server (MS did buy SQL Server from Sybase originally, correct?) because the command is unrecognized when I run it in SQL.
declare @mytemp table (myfield varchar(2))

is used to declare a table variable and not temporary tables; you create temporary tables with

create table #mytemp (myfield varchar(2))

table variables reside only in memory so they are never created in tempdb. temporary tables however are dropped when the batch that created them is finnished no metter if ungracefully or not.

I recommend you before restart your server just right click on the tempdb database and click "Refresh". You migth see all your temp tables dessapearing.
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Gautham JanardhanCommented:
Table variables like declare @mytemp table (myfield varchar(2))
reside in the memory (RAM) unless and until thers enuf space. IF thers no space enuf in the ram only its created in the Tempdb.

Ur tempdb will be cleared of these tables when the connection which created it is finished.

Also make sure that u drop ur temp tables after its use

Optimizing tempdb Performance
General recommendations

Allow the tempdb database to automatically expand as needed. This ensures that queries that generate larger than expected intermediate result sets stored in the tempdb database are not terminated before execution is complete.

Set the original size of the tempdb database files to a reasonable size to avoid the files from automatically expanding as more space is needed. If the tempdb database expands too frequently, performance can be affected.

Set the file growth increment percentage to a reasonable size to avoid the tempdb database files from growing by too small a value. If the file growth is too small compared to the amount of data being written to the tempdb database, then tempdb may need to constantly expand, thereby affecting performance.

Place the tempdb database on a fast I/O subsystem to ensure good performance. Stripe the tempdb database across multiple disks for better performance. Use filegroups to place the tempdb database on disks different from those used by user databases.
Anthony PerkinsCommented:
>>table variables reside only in memory so they are never created in tempdb.<<
Actually no.  Variables of type table can also use TempDB.
tomandlisAuthor Commented:
Guys, your missing the point.  I want to drop the orphaned tables w/o having to restart the server.  I don't know how they got there, I just want them gone.
Anthony PerkinsCommented:
>>Guys, your missing the point.<<
I think there is an extra "s" there.  I was not responding to you and I have no idea why you have temporary tables remaining in tempdb, I have never seen that or heard of it.  Perhaps they were global temporary tables, but even then they should not remain there for long
tomandlisAuthor Commented:
acperkins, dude you've got to back off the meds.

Scott, thanks for the suggestion.  When I tried you command I received the following error:

Invalid object name '#040BCBA4' (or whatever table name I try)

I tried it w/o the '#' and by putting the table in brackets but I got the same thing.

The temp db is 2.5 gb in size and 1.9gb of that is in the .ldf log file!  I'm not sure why the log is so big because I thought tempdb didn't log stuff (at least not permanently)

Thanks for the keyword(s) "left-over" that turned up this link which is exactly my problem, but there is no solution posted:


At that link I found this query (below) which showed that all the temp tables were created today!
select * from tempdb.dbo.sysobjects where name like '#%' order by crdate desc

I'm thinking that maybe these tempdb tables aren't my problem.  My problem is the size of the tempdb.ldf file.  That file might be the so big because of these tables, but .... man I'm stuck.  MS Bug??

Scott PletcherSenior DBACommented:
Possibly, but not necessarily.  An active transaction needs its entire virtual log as long as it is active.

Run these commands:

USE tempdb

just to make sure there's no open transactions.  If not, you can shrink the tempdb log:

DBCC SHRINKFILE ( templog , 100 )

0.1% of the time this may cause a running task to abend.  But generally it works fine, and avoids having to recycle SQL.
tomandlisAuthor Commented:
The answer to the question was that sql server 2005 has changed and they now have a 'delayed drop' for temporary tables.  So, in other words the tables were there by design and not the result of my forgetting to clean them up in a stored procedure.  

As far as deleting them, you do so at your own risk.  Sql Server may be using the tables for one reason or another and if you drop them you might break something and/or slow something down.  Additionally, I could never seem to get the rights to drop them even though I'm sa.  Moreover, I couldn't even do selects on them to see what was inside the table (to see if it was a candidate for deletion).

I waited 24 hours and SQL was true to their manual and the tables slowly disappeared which was good, but it meant that, essentially, the solution is buy more scsi drives and move your tempdb to the new drive because sql server 2005 wants a bigger footprint.  Fine with me, hard drive space is cheap and if it buys me improved performance then I'll do it.  But, very very fustrating, naughty Bill!

No one really answered the question, but Scott was the most helpful and his answers led me in the right direction
Anthony PerkinsCommented:
>>but Scott was the most helpful and his answers led me in the right direction<<
Than please close the question by awarding points to him.  Here's how:

How do I accept a comment?

tomandlisAuthor Commented:
I'd like to give Scott the points, but I don't want to check off his answer(s) as 'accepted' because this would indicate they were correct.  They were helpful, but they weren't correct.  My final post had the 'correct' answer.  

Whenever I review questions at your site (either asked by me or others) the first thing I look at is the 'accepted' answer.  I hate it when the 'accepted' answer is wrong.  Think about it, it completely ruins the purpose of the thread to check an incorrect answer as 'correct'--it will also reduce the value of your site to allow this to happen IMHO.

I just want to give him the points---I don't wish to say he was correct!
Anthony PerkinsCommented:
>>Whenever I review questions at your site (either asked by me or others)<<
Correction:  It is our site:  I am member just like Scott and you are.

>>I just want to give him the points---I don't wish to say he was correct!<<
Than we will just have to wait until a Moderator decides, won't we?
tomandlisAuthor Commented:
"Than we will just have to wait until a Moderator decides, won't we?"

Won't we??  There is no reason to get snide about it.  I'm a heavy user of your site and I'm trying to help you guys.  I just suggested a way that you could improve your service and this is the thanks I get.  Great, just great.

My apologies to Scott for putting up with this.  BTW, IMHO it IS your site, and unless you take ownership of the content it will never improve.  
Anthony PerkinsCommented:
>>BTW, IMHO it IS your site, and unless you take ownership of the content it will never improve. <<
I am afraid not.  It is our site.  I have no more stake in it than you do.  I am just a member like you are and I am sorry you cannot see that.  But whatever works for you.

Good luck.
Anthony PerkinsCommented:

And this:
"I have no more stake in it than you do."
Should have read:
I have just as much stake in it as you do.

>>There is no reason to get snide about it.<<
I am sorry you took it that way, it was not intended as 'snide", but rather factual.  The way it works here is that a Moderator will be here in the next few days to finalize this question.

>>and I'm trying to help you guys. <<
 The best help you can give this site and more importantly to the EE Community, is not "Abandon your question and leave the mess for someone else to clean up".  By the way, that is not my quote it is from the EE Guidelines. See here:
What are my choices?

Here is another of your questions that appears to have fallen through the cracks:
09/19/2006 125 Toad Connection Window Locks Up Won't Cl...  Open Oracle
tomandlisAuthor Commented:
Dude you're crazy if you're doing this clean-up work and not getting anything in return.  If you are getting something in return (points? resume fodder?) then, no matter how small the compensation, you've just taken ownership (or employeeship at the least).  I'll assume the latter, i.e., that you're not crazy and you do have some stake in e-e.

I looked at the options available at http://www.experts-exchange.com/help.jsp#hi67 and there should be one more.  Per my assumption above, I will tell you and let you pass it on to whomever:

'accept my answer/comment, but award points to someone who was helpful'

If that was available then I wouldn't have to abandon this question.
I agree with tomandlis.

In most of the cases the people abandon their question because there is no right answer attached to them and they are still waiting for it. The problem is that a question becomes old and the answerers loose their interest.

There are 2 solution for this:

1. The person who asked can close the answer like tomandlis suggests with a "close answer" clause.
2. Leave them opened so that maybe somebody will pick them from the queue and answer them, even if it takes longer.

I am thinking of a system with stars, 1 to 5 or to 10, for each answer and the first 3 ranked answers to share the points (not necessarily in equal amounts) if the question remain open until the end of the preestabilished term.
Anthony PerkinsCommented:
I am sure these are all very worth suggestions, but totally off-topic and would be best served if posted in a more appropriate Topic Area such as:
Anthony PerkinsCommented:
>>Dude you're crazy if you're doing this clean-up work and not getting anything in return.<<
I beleive T-Shirts were promised in 2001 :)
tomandlisAuthor Commented:
AC, thanks for the backup and stars sound good and here's my final 2 cents.  I think whatever the solution there clearly needs to be a way to select the CORRECT answer because that is what your typical search engine user is looking for.  IMHO, that is who we're (there, are you happy, I just took ownership) trying to please--search engine users.

There also needs to be a way to separate CORRECTness from POINTS.  This question is typical of my E-E experience, I am usually the one with the correct answer and others were helpful.  I don't want to stiff the guys who helped me because usually I couldn't have arrived at the right soln w/o their help.  Allow people to select their own answers as correct, but if they do so they must distribute the points to someone.

Anthony PerkinsCommented:
Please skip me and award points to ScottPletcher instead.
Scott PletcherSenior DBACommented:
Odd suggestion for awarding points.  Did you read the thread at all prior to making a recommendation?
Anthony PerkinsCommented:
Sorry about that.  I should have made my recommendation when pinaldave asked two weeks ago.
Scott PletcherSenior DBACommented:
No, I'm very sorry -- I was talking about pinaldave *only*.
I apologize for confusion. I re-read the question and I think points must be awared to ScottPletcher.
I would be more careful in future.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.