Solved

drop orphan temporary tables in sql server 2005

Posted on 2006-10-22
30
1,417 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
ID: 17783724
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
ID: 17783977
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
ID: 17786449
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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17786450
>>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
ID: 17788249
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
ID: 17788846
>>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:
Scott Pletcher earned 90 total points
ID: 17789062
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
ID: 17791180
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:Scott Pletcher
ID: 17791275
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
ID: 18062959
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
ID: 18065870
>>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
ID: 18071676
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
ID: 18073731
>>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
 

Author Comment

by:tomandlis
ID: 18075745
"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
ID: 18078992
>>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
ID: 18079048
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
ID: 18083841
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
ID: 18085260
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
ID: 18087360
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
ID: 18087373
>>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
ID: 18091646
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
ID: 18153664
Please skip me and award points to ScottPletcher instead.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 18158872
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
ID: 18160384
Sorry about that.  I should have made my recommendation when pinaldave asked two weeks ago.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 18160792
No, I'm very sorry -- I was talking about pinaldave *only*.
0
 
LVL 21

Expert Comment

by:pinaldave
ID: 18189637
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

786 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