Solved

drop orphan temporary tables in sql server 2005

Posted on 2006-10-22
30
1,409 Views
Last Modified: 2010-05-18
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:

#0012EA35
#0047F45F
#01070E63
...

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

#myTempTable_______0000019
#myOtherTempTable_______0000013d
...


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.
0
Comment
Question by:tomandlis
  • 10
  • 8
  • 4
  • +3
30 Comments
 

Author Comment

by:tomandlis
Comment Utility
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.
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
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.
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
Comment Utility
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.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>table variables reside only in memory so they are never created in tempdb.<<
Actually no.  Variables of type table can also use TempDB.
0
 

Author Comment

by:tomandlis
Comment Utility
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.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>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
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 90 total points
Comment Utility
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:

SELECT 'DROP TABLE ' + CAST(name AS VARCHAR(200))
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?
0
 

Author Comment

by:tomandlis
Comment Utility
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:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=308779&SiteID=17

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



0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
Possibly, but not necessarily.  An active transaction needs its entire virtual log as long as it is active.

Run these commands:

USE tempdb
DBCC OPENTRAN

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.
0
 

Author Comment

by:tomandlis
Comment Utility
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
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>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?
http://www.experts-exchange.com/help.jsp#hi68

Thanks,
Anthony
0
 

Author Comment

by:tomandlis
Comment Utility
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!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>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?
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:tomandlis
Comment Utility
"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.  
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>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.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
P.S.

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?
http://www.experts-exchange.com/help.jsp#hi67

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
http://www.experts-exchange.com/Databases/Oracle/Q_21994986.html
0
 

Author Comment

by:tomandlis
Comment Utility
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.
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
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.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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:
http://www.experts-exchange.com/Community_Support/Suggestions/
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>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 :)
0
 

Author Comment

by:tomandlis
Comment Utility
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.

0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Please skip me and award points to ScottPletcher instead.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
Odd suggestion for awarding points.  Did you read the thread at all prior to making a recommendation?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Sorry about that.  I should have made my recommendation when pinaldave asked two weeks ago.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
No, I'm very sorry -- I was talking about pinaldave *only*.
0
 
LVL 21

Expert Comment

by:pinaldave
Comment Utility
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.

Regards,
---Pinal
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now