Post reply

Non-user created temporary tables in Tempdb are in the thousands

  • April 18, 2012 at 10:40 am

    #255042

    I see thousands of tables if I expand temporary tables under tempdb. All of them have names like dbo.#010089E0, dbo.#7FEC6A16, etc. These are not user created temp tables. Our tempdb size is 190GB so there is no issue of tempdb getting full. Almost all of them have created dates of today when I checked sys.tables.

    Cannot find anything online about this. Any ideas on how to avoid this and how to get them to self drop when they no longer needed.

    Our database is quite large(> 1 TB) and sees heavy activity but these tables still there when there is 0 activity and no processes running or any user activity.

  • April 18, 2012 at 10:42 am

    #1475489

    Work tables, table variables (a guess on this one, but seem to remember reading this somewhere).

  • April 18, 2012 at 10:47 am

    #1475494

    Not work tables.

    Table variables or the cached shells of temp tables.

    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 3 posts - 1 through 2 (of 2 total)

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