December 30, 2008 at 1:22 pm
I have some temp tables in my system tempdb that I need to kill.
They are called #SOMEHEXNumber etc...
I can not do just a "drop table #SOMEHEXNumber ". I have tried some suggestions found here in the If exists statements but no deal.
If I reboot they go away. Is there a method of killing these tables without rebooting?
Thanks
December 30, 2008 at 1:32 pm
Those are table variables. I would guess they are used quite often.
Don't worry about them. SQL 2005 has a caching mechanism for temp tables. If it knows the temp table is used very often, the metadata and 1st page allocation are not deleted when the table is dropped. This is an optimisation designed to reduce the overhead of frequently created and dropped temp tables/table vars.
It's mentioned in one of the Inside SQL Server 2005 books. I think the Query Optimisation one.
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
December 30, 2008 at 1:36 pm
"table variables"?
These tables are taking 30GB of space for the tempdb.mdf. Will SQL eventually delete these?
December 30, 2008 at 1:43 pm
Warren Peace (12/30/2008)
"table variables"?
DECLARE @SomeVar TABLE (id int)
They appear in tempDB's system tables as something like #08EA5793
These tables are taking 30GB of space for the tempdb.mdf. Will SQL eventually delete these?
If they're not used again it should. If they are used, it should reuse them.
How are you checking space usage in TempDB?
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
December 30, 2008 at 1:48 pm
I checked the db properties and the physical size of the tempdb.mdf.
If they do not decline in size, what is the process to kill the tables other than rebooting?
Thanks
December 30, 2008 at 1:58 pm
Warren Peace (12/30/2008)
I checked the db properties and the physical size of the tempdb.mdf.
Doesn't mean the tables are using 30GB. It just means that at some time the total contents of tempDB were 30GB.
TempDB's used for a lot more than just temp tables, especially on SQL 2005 and higher.
Interim sorts and aggregations, online index rebuilds, row version store for snapshot isolation and triggers, etc
If they do not decline in size, what is the process to kill the tables other than rebooting?
Restart the SQL service.
The TempDB data file won't shrink by itself. Data files never do (unless they have autoshrink on, which is a really bad idea)
Growing a data file is an expensive operation and all databases need some free space within them. If your TempDB is currently 30GB, that means it needs to be at least 30GB for the normal operation of your server.
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
December 30, 2008 at 2:03 pm
"Restart the SQL service."
I knew you were going to say that.
What drew it to my attention is the test server ran out of space. I increased it to add a free space of 100GB so let's see what happens.
Thanks
December 30, 2008 at 2:03 pm
GilaMonster (12/30/2008)
DECLARE @SomeVar TABLE (id int)They appear in tempDB's system tables as something like #08EA5793
OMG, I cannot believe some of the stuff that you know Gail! How on earth did you learn this?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 30, 2008 at 2:06 pm
GilaMonster (12/30/2008)
Restart the SQL service.The TempDB data file won't shrink by itself. Data files never do (unless they have autoshrink on, which is a really bad idea)
Just to clarify -
TempDB files don't shrink while the service is running like Gail mentioned. However, during a restart of the service, TempDB is rebuilt in its entirety, so it reverts back to whatever its initial size is set to.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 30, 2008 at 2:07 pm
lol...
I was thinking the same thing.
She must speak in HEX.
December 30, 2008 at 2:11 pm
RBarryYoung (12/30/2008)
GilaMonster (12/30/2008)
DECLARE @SomeVar TABLE (id int)They appear in tempDB's system tables as something like #08EA5793
OMG, I cannot believe some of the stuff that you know Gail! How on earth did you learn this?
I was investigating the myth that table variables don't appear in the TempDB system 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
December 30, 2008 at 2:42 pm
In which table you find it eventually as I can't see any entries for temp variable in syscacheobjects.
TIA.
MJ
December 30, 2008 at 3:06 pm
SYS.TABLES will show them if they're still "around". On well-behaved servers, you may need a WAITFOR to keep the batch "open" long enough to see the table var. Actually - any of the following will show you the ones in effect at that moment (from within TempDB):
select * from sys.tables
select * from sys.internal_tables
select * from information_schema.tables
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 30, 2008 at 11:52 pm
HI,
Temporary table's are limited to particular session in which they are created when session end they automatically dropped... but if you want to perform the task to drop that temporary table then you need to drop that temporary table into session in which they created like try to open new query window create temp table into that ,now open new query window and try to drop your created table it will show you following error
Cannot drop the table '#rtemp', because it does not exist or you do not have permission.
that means that currently table which you are trying to drop is not exist in this session
now goto query window in which you create table and try to drop it will successfully done
i think you will understand from this example...
Raj Acharya
December 31, 2008 at 12:05 am
MANU (12/30/2008)
In which table you find it eventually as I can't see any entries for temp variable in syscacheobjects.
You won't see them in syscacheobjects. That's for execution plans.
select name from tempdb.sys.tables
go
DECLARE @SomeTable TABLE (id int)
select name from tempdb.sys.tables
go
CREATE TABLE #SomeTable (id int)
select name from tempdb.sys.tables
DROP TABLE #SomeTable
GO
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 15 posts - 1 through 15 (of 43 total)
You must be logged in to reply to this topic. Login to reply