SQL Server Performance

Temp tables not being removed automatically.

Discussion in 'SQL Server 2005 General DBA Questions' started by Haywood, Nov 10, 2005.

  1. Haywood New Member

    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.
  2. ranjitjain New Member

    Are you saying this happens in SQL 2005 or 2k.
  3. FrankKalis Moderator

    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)
  4. Haywood New Member

    Yes, this is 2005 with local temp tables. I going to try reproducing it this weekend on another server.
  5. Haywood New Member

    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...
  6. satya Moderator

    [<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>
  7. mulhall New Member

    This is an interesting occurence, if you could post up the cause when you identify it I'd appreciate it.
  8. Haywood New Member

    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=':)' />
  9. derrickleggett New Member

    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
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  10. mulhall New Member

    Yeah, I'd say their not being dropped because the session is still active.
  11. Haywood New Member

    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
    derrickleggett@hotmail.com

    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.

Share This Page