Strange Tables In TempDB

  • I was trying to find out why my TempDB is as large as it is, and found some strange tables in the database. I have two permanent tables ("volume" tables defined in Model so they are rebuilt on start-up). I also found the expected temp tables with names like "#working________________...______00000000B43A" that come and go as they are created and dropped.

    Then there 145 tables with eight-character names:

    SELECTid, crdate, name

    FROMsys.sysobjects

    WHERExtype = 'U'

    ORDER BY crdate

    /*

    id crdate name

    ----------- ----------------------- -------------------

    2105058535 2010-01-27 21:07:20.050 VolumeTree

    21575115 2010-01-27 21:11:44.627 VolumeTreeWeek

    1527428961 2012-03-26 05:00:49.600 #5B0AB761

    1543429018 2012-03-26 05:01:02.617 #5BFEDB9A

    1655429417 2012-03-26 05:05:00.907 #62ABD929

    1671429474 2012-03-26 05:30:00.747 #639FFD62

    1735429702 2012-03-26 05:34:46.063 #67708E46

    1751429759 2012-03-26 05:55:07.713 #6864B27F

    1767429816 2012-03-26 05:55:16.757 #6958D6B8

    1783429873 2012-03-26 05:55:16.760 #6A4CFAF1

    1799429930 2012-03-26 05:55:16.760 #6B411F2A

    1815429987 2012-03-26 05:55:16.760 #6C354363

    1107951469 2012-03-26 05:58:20.813 #4209FF6D

    1123951526 2012-03-26 05:58:20.813 #42FE23A6

    1139951583 2012-03-26 05:58:20.817 #43F247DF

    1155951640 2012-03-26 05:58:20.817 #44E66C18

    1187951754 2012-03-26 05:58:20.820 #46CEB48A

    1171951697 2012-03-26 05:58:20.820 #45DA9051

    1251951982 2012-03-26 05:58:20.823 #4A9F456E

    ...

    237217337 2012-04-04 18:58:21.603 #0E23A639

    285217508 2012-04-04 18:58:23.810 #110012E4

    301217565 2012-04-04 18:58:23.813 #11F4371D

    685218933 2012-04-04 19:16:16.667 #28D79C75

    941219845 2012-04-04 19:23:29.400 #3819E005

    957219902 2012-04-04 19:23:29.400 #390E043E

    */

    I have run serveral searches but I cannot find an explanation for what those tables are. I would appreciate someone pointing me in the right direction.

  • Lots of people have looked but no one has commented. Is there no one who has any idea what these tables are?

  • Don't take this wrong, but that's not nearly enough information for anyone to come to any conclusion about what might be going on with your database. What does your database do? Do you have jobs running? Are you importing data somewhere? There could be perfectly valid reasons for you to have some temporary tables, but from here it's hard to tell. Did this start recently? Has anything changed on the computer between when things worked and when they didn't?

    Sorry, you've just posted an unanswerable question. It would be helpful if you posted more background about what's going on, or at least your setup, so someone can at least narrow down the cause.

  • Perhaps I need to rephrase the question. What can create a table in the TempDB other than "CREATE TABLE #MyTempTable"?

    When I explicitly create a temp table, the table name comes out with a long series of underscores as part of the table name when viewed in sys.sysobjects on TempDB. These tables do not have those underscores, which tells me they were not created using "CREATE TABLE #" in user code.

    So, if I didn't explicitly create them, what other process could be creating tables in TempDB, especially those that hang around for several days?

    To answer pietlinden's question above, this is a transactional database handling account management, order creation and fulfillment, commission payments, etc. We have automated jobs running, but on an hourly schedule that does not match the creation dates of the tables. We do have a user website, but that code should be generating user temp tables with the underscores, not (apparently) system-generated tables with fixed-length names.

  • Multiple table joins, sorting are two activities that may create work tables in tempdb.

  • Lynn Pettis (4/8/2012)


    Multiple table joins, sorting are two activities that may create work tables in tempdb.

    Are those tables named similarly to what I have described above? Do those tables persist for the duration of the connection or are they "garbage collected" (for lack of a better term) before the connection terminates? Is it normal to have those tables hanging around and I've simply never noticed them before?

  • SQL Server creates a lot of Temp Tables to handle queries for the system and other things. They all look like # followed by some number. I wouldn't be too concerned unless their sizes start to get out of hand.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Perhaps http://technet.microsoft.com/library/Cc966545 or http://msdn.microsoft.com/en-us/library/ms176029(v=sql.105).aspx will help.

    Randy
    Helpdesk: Perhaps Im not the only one that does not know what you are doing. 😉

  • I think the ones with names like "#5B0AB761" are from table variables. If they hang around a long time, perhaps you should check for long running queries that would keep them in scope.

    Randy
    Helpdesk: Perhaps Im not the only one that does not know what you are doing. 😉

  • Tables named things like #A453DF43 are either table variables or are the shells of cached 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 10 posts - 1 through 9 (of 9 total)

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