April 4, 2012 at 5:46 pm
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.
April 8, 2012 at 5:20 pm
Lots of people have looked but no one has commented. Is there no one who has any idea what these tables are?
April 8, 2012 at 6:01 pm
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.
April 8, 2012 at 7:52 pm
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.
April 8, 2012 at 8:39 pm
Multiple table joins, sorting are two activities that may create work tables in tempdb.
April 8, 2012 at 9:14 pm
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?
April 8, 2012 at 9:55 pm
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
Change is inevitable... Change for the better is not.
April 9, 2012 at 10:07 am
Perhaps http://technet.microsoft.com/library/Cc966545 or http://msdn.microsoft.com/en-us/library/ms176029(v=sql.105).aspx will help.
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
April 9, 2012 at 10:13 am
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.
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
April 9, 2012 at 10:50 am
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply