Post reply

TempDB - Temporary Tables whey are there loads of system generated #tables

  • May 25, 2016 at 12:52 am

    #310901

    Looking in TempDB there is a load of Temporary tables dbo.#A2345678 (any combintaion of 8 letters and numbers).

    Now they surely are not created by a user to store data in so what are they there for?

    Cheers

    E

  • May 25, 2016 at 1:06 am

    #1881252

    Hi,

    in this link is explained, which data is stored in the tempDB:

    https://msdn.microsoft.com/en-us/library/ms190768.aspx

    Internal objects that are created by the SQL Server Database Engine, for example, work tables to store intermediate results for spools or sorting.

    Kind regards,

    Andreas

  • May 25, 2016 at 3:47 am

    #1881265

    Cheers,

    I think that answers the question. I looked at some of the objects and they had all been create today so must be temporary work objects created by the system.

    Cheers

    E

  • May 25, 2016 at 4:41 am

    #1881276

    More likely to be user-created. SQL caches temp tables. To reduce the work needed to allocate new temp tables, when one is dropped (under some circumstances) it is cached as an empty table with a name of that form.

    In addition, table variables, including ones created by multi-statement UDFs, always have names of that form.

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

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