Post reply

temp tables exist in tempdb under folder : "Temporary Tables".

  • August 10, 2017 at 7:26 pm

    #339306

    Hi,

    Technically once the session of procedure or other adhic query session is closed the temp table which is created in tempdb is supposed to be removed. But I see 1000+ temp tables.

    If I try to clean them, getting the error.

    Msg 3701, Level 11, State 5, Line 38
    Cannot drop the table '#A0163670', because it does not exist or you do not have permission.

    Thanks.

  • August 11, 2017 at 1:11 am

    #1954918

    Only the session that created a temp table can drop it, or look at it in any way.

    They're probably cached temp tables, not ones in use. SQL caches temp tables for efficiency.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply