Temp tables not being removed automatically. | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Temp tables not being removed automatically.

Has anyone else noticed this behavior? I’m seeing local temporary tables still created when the calling connection has disconnected. The behavior is that it should drop the local temp table on a disconnect but it’s defenitly not.<br /><br />I know it’s good practice to always explicitly drop temp tables (and I do), but not everybody follows the rules all the time. <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /><br /><br /><br />Thanks.
Are you saying this happens in SQL 2005 or 2k.
To the best of my knowledge haven’t I seen such a behaviour before. As you are sure, that these are local temp tables, can you narrow down this issue to certain circumstances or does this happen randomly? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
Yes, this is 2005 with local temp tables. I going to try reproducing it this weekend on another server.
Seems to be related to this one specific server. I cannot duplicate it on other servers of the same build.
One more reason why people outside the administrative staff should not be building thier own servers…
[<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />] agree on the last comment.<br />Strange to see that behaviour on one instance only, I cannot reproduce the error at my end and it is working as expected. Can you just capture the results and temp tables existence during and after the process on this server.<br /><br />PS: I have received your email and I will reply soon with details.<br /><br /><hr noshade size="1"><b>Satya SKJ</b><br />Contributing Editor & Forums Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided “AS IS” with no rights for the sake of <i>knowledge sharing.</i></font id="size1"></font id="teal"></center>
This is an interesting occurence, if you could post up the cause when you identify it I’d appreciate it.
Well, I would put some more time & effort into this, but considering that this machine has every piece of software known to MS on it I’m not really going to investigate too far. It’s also the only one out of six that I tested on that had this problem. The machine has defenite network issues, that I do know for sure.<br /><br />I blame the dev guys. Who knows what they’ve done to this poor little machine.. <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />
Have you checked for open connections when these temp tables are still setting out there? Also, is there a version of VS installed? If so, which one? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
Yeah, I’d say their not being dropped because the session is still active.
quote:Originally posted by derrickleggett Have you checked for open connections when these temp tables are still setting out there? Also, is there a version of VS installed? If so, which one? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
Nope, no hung or ghosted connections. There is VS installed, 2005 that gets installed with the SQL client tools install. I was using the SQLManager to create temp tables and not VS.
]]>