Strange Temp Tables

Question

Post reply

Strange Temp Tables

mvillegascuellar

SSC Eights!

Points: 881
More actions
December 15, 2010 at 10:20 am

#144026

Hi,
I have a doubt about temporary tables. One of my development servers has a tempdb which is about 3.5GB, and that was odd because it usually is under 100MB, so I checked the sysobjects of my tempdb filtered by xtype='U' and I found a lot of objects with strange names, not like the regular names like #objectname_______HEX000, which according to the creation date are over a week old. These objects names ar like "#0348EEDB"
Can someone explain me what are these objects? and how can I get rid of them?, I think this is why my tempdb is that big.
Thanks for your help.;-)

Viewing 13 posts - 1 through 12 (of 12 total)

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

Those are table variables. The only way to get rid of them is for the batch that declared them to stop running.

I doubt that they are the cause of why your tempdb is up to 3.5gb, though it could be.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

mvillegascuellar SSC Eights! Points: 881 More actions · Answer 2

Hi Wayne,

I really think they are not variable tables, because no user process is running on the server and the still remain, and as I said the are over a week old.

What else can they be?

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 3

Hi

This are temporary tables ,these tables are created such as running stored procedures with Temp tables.If the stored procedure fails then you will be facing this type of problems.It means that Temp tables are not closed properly then and there in same session thats why you are having more tables in tempDB.You cant drop temp tables as normal tables[Drop table Table_Name].

You can use following steps to free Tempdb Space

a.) Determine the logical file names of the tempdb database and their current location on the disk.

SELECT name, physical_name AS CurrentLocation

FROM sys.master_files

WHERE database_id = DB_ID(N'tempdb');

GO

b.)Change the location of each file by using ALTER DATABASE.

USE master;

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = templog, FILENAME = 'F:\SQLLog\templog.ldf');

GO

c.)Stop and restart the instance of SQL Server.

d.)Verify the file change.

SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id = DB_ID(N'tempdb');

e.)Delete the tempdb.mdf and templog.ldf files from the original location of the Past.

Thanks

Parthi

Thanks
Parthi

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

parthi-1705 (12/15/2010)
This are temporary tables ,these tables are created such as running stored procedures with Temp tables.If the stored procedure fails then you will be facing this type of problems.It means that Temp tables are not closed properly then and there in same session thats why you are having more tables in tempDB.You cant drop temp tables as normal tables[Drop table Table_Name].

Temp tables are dropped automatically by SQL as soon as the proc they were created in ends (however it ends) or the connection that created them closes.

a.) Determine the logical file names of the tempdb database and their current location on the disk.
b.)Change the location of each file by using ALTER DATABASE.
c.)Stop and restart the instance of SQL Server.
d.)Verify the file change.
e.)Delete the tempdb.mdf and templog.ldf files from the original location of the Past.

Wow. Just wow.

Restarting SQL would set TempDB back to it's default size. The rest is unnecessary.

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

mvillegascuellar (12/15/2010)
I really think they are not variable tables

Those are table variables, that's how SQL names them.

SQL can cache temp tables if they're used a lot, but there's no data stored with them and they won't take up space.

How are you determining the space? Just the size of the file? If so, it could have grown that large due to some operation, that operation completed and the tables it was using dropped. TempDB won't automatically shrink back down, no database does.

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

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 6

GilaMonster (12/15/2010)
a.) Determine the logical file names of the tempdb database and their current location on the disk.
b.)Change the location of each file by using ALTER DATABASE.
c.)Stop and restart the instance of SQL Server.
d.)Verify the file change.
e.)Delete the tempdb.mdf and templog.ldf files from the original location of the Past.
Wow. Just wow.
Restarting SQL would set TempDB back to it's default size. The rest is unnecessary.

Please have a look at help file in SQL Server 2008

ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/72bb62ee-9602-4f71-be51-c466c1670878.htm

on A. Moving the tempdb database Topic

Thanks

Parthi

Thanks
Parthi

mvillegascuellar SSC Eights! Points: 881 More actions · Answer 7

Thank you very much to all of you,

I´ve finally know what these things are, Thank you parthi-1705 and GilaMonster, for clearing this up to me. I tested what you told me about a temp table inside a procedure and that if it fails before dropping the temp table, it remains with this strange name.

But I also would like to contribute with one more thing I´ve noticed regarding temp tables, if I change the stored procedure, at least by adding a single enter, and recompile it, the temp tables with strange names asociated with it disapear from the tempdb.

I just like to add a final THANK YOU.:-D

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

parthi-1705 (12/15/2010)
Please have a look at help file in SQL Server 2008
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/72bb62ee-9602-4f71-be51-c466c1670878.htm
on A. Moving the tempdb database Topic

I'm familiar with moving tempDB. The question is why are you advising him to move it at all?

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

mvillegascuellar (12/15/2010)
I´ve finally know what these things are, Thank you parthi-1705 and GilaMonster, for clearing this up to me. I tested what you told me about a temp table inside a procedure and that if it fails before dropping the temp table, it remains with this strange name.

I think you're misunderstanding what you're seeing there.

As I mentioned, SQL caches the definitions of temp tables to reduce the cost of recreating it at a later date when the proc is called again. There is no data retained and they take up virtually no space. That's what you're seeing here, not the failure to drop the table because of an error

You will see the same caching if the DROP TABLE is executed.

CREATE PROCEDURE TestingTempTables

AS

CREATE TABLE #test (

ID INT

)

DROP TABLE #test -- properly dropped

PRINT 'done'

GO

SELECT NAME FROM tempdb.sys.tables AS t

EXEC TestingTempTables

SELECT NAME FROM tempdb.sys.tables AS t

Again I want to ask how you're measuring the size of TempDB. If all you're looking at is the file size, whatever grew the DB could have happened some time back, been completely cleaned up and left no trace at all.

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

mvillegascuellar SSC Eights! Points: 881 More actions · Answer 10

Now I get what you are saying, since I`ve tested your code. Well to answer your question I was messuring the size of this temp tables by running this query:

select * from sys.dm_db_partition_stats where object_id = 133575514

And in some temp tables the column row_count showed a number higher than 0. That is what I still don´t understand if you are telling me that SQL Server only saves tables definitions why the row_count was higher than 0?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

How much higher than zero?

Are you absolutely sure there are no user connections of any 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

mvillegascuellar SSC Eights! Points: 881 More actions · Answer 12

You are right Mr. GilaMonster,

I just checked again and all this temp tables have 0 rows, it seams that I read the wrong column, I queried the used_page_count column instead of the row_count column. Thank you very much for your help.