December 31, 2008 at 2:49 am
Warren Peace (12/30/2008)
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
I think temp table automaticaly deleted when session is over.
Otherwise u have to restart service which create a new tempdb.
January 2, 2009 at 1:17 am
so if i can't delete temp tables from my session (query analyzer) what are my options to delete those tables that i know eat up my free space and they are not in used(orphaned).
i can't do every time restart to the sql server service.
can't i run any query that will see on what session those temp tables was create and then use this session to delete the temp table?(or any other idea)
THX
January 2, 2009 at 2:05 am
Mad-Dog (1/2/2009)
so if i can't delete temp tables from my session (query analyzer) what are my options to delete those tables that i know eat up my free space and they are not in used(orphaned).
SQL 2000 or 2005?
can't i run any query that will see on what session those temp tables was create and then use this session to delete the temp table?(or any other idea)[/quote]
When the connection that created them is closed, they will be deleted (on SQL 2000). One 2005, the metadata may be cached, but that will take up minimal space (max 8k per temp table)
Are you seeing large temp tables staying around for a long time?
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
January 2, 2009 at 2:45 am
GilaMonster (1/2/2009)
Mad-Dog (1/2/2009)
so if i can't delete temp tables from my session (query analyzer) what are my options to delete those tables that i know eat up my free space and they are not in used(orphaned).SQL 2000 or 2005?
can't i run any query that will see on what session those temp tables was create and then use this session to delete the temp table?(or any other idea)
When the connection that created them is closed, they will be deleted (on SQL 2000). One 2005, the metadata may be cached, but that will take up minimal space (max 8k per temp table)
Are you seeing large temp tables staying around for a long time?[/quote]
If temp tables not take much memory then why we need to delete temp table even if they automaticaly deleted?
January 2, 2009 at 3:52 am
SQL 2005.
yes i'm seeing that my tempdb database sometimes grow for 40-50GB.
even if i wait few hours and try to shrink the tempdb data file i can't.
January 2, 2009 at 4:50 am
Mad-Dog (1/2/2009)
SQL 2005.yes i'm seeing that my tempdb database sometimes grow for 40-50GB.
even if i wait few hours and try to shrink the tempdb data file i can't.
I advice to u to drop temp tables after creating it, or check if any cursors are not remain to close/deallocate, and for read only operation use only FAST_FORWARD cursor.
January 2, 2009 at 5:21 am
Mad-Dog (1/2/2009)
SQL 2005.yes i'm seeing that my tempdb database sometimes grow for 40-50GB.
even if i wait few hours and try to shrink the tempdb data file i can't.
There's a possibility that whatever created the temp table runs as a service and maintains a connection with the SQL Server. Since the temp table is dropped automatically only when the connection is closed this may be a case of an impolite application that believes it is the only thing using the SQL Server.
I'm not sure if there's a way to determine the creator/owner of that temp table but you could try running Profiler for a few minutes against tempdb to see who's accessing it.
January 2, 2009 at 5:23 am
Warren Peace (12/30/2008)
lol...I was thinking the same thing.
She must speak in HEX.
So now we know - Gail's smart AND hexy! :w00t:
January 2, 2009 at 5:31 am
Mad-Dog (1/2/2009)
SQL 2005.yes i'm seeing that my tempdb database sometimes grow for 40-50GB.
That's not what I asked. I asked if you're seeing large temp tables that stay around for a long time.
TempDB is used for a lot more than just temp tables. Query's work tables go in there, the row version store is in TempDB. It's used for index rebuilds, checkDB and more.
even if i wait few hours and try to shrink the tempdb data file i can't.
You won't be able to shrink tempDB while it's in use. In fact, you shouldn't try, you can cause problems doing so.
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
January 2, 2009 at 5:48 am
DonaldW (1/2/2009)
Mad-Dog (1/2/2009)
SQL 2005.yes i'm seeing that my tempdb database sometimes grow for 40-50GB.
even if i wait few hours and try to shrink the tempdb data file i can't.
There's a possibility that whatever created the temp table runs as a service and maintains a connection with the SQL Server. Since the temp table is dropped automatically only when the connection is closed this may be a case of an impolite application that believes it is the only thing using the SQL Server.
I'm not sure if there's a way to determine the creator/owner of that temp table but you could try running Profiler for a few minutes against tempdb to see who's accessing it.
I think it's not happen due to tamp tables.it happen due to checkdb,update statostics , index rebuilding/reindexing.
January 2, 2009 at 8:44 am
You can also try a DBCC FREEPROCCACHE to blow out all the work tables, cached temp tables etc.
January 2, 2009 at 9:12 am
I should have added that while freeing the proc cache will clear the symptoms you are seeing temporarily the root cause is likely proc cache bloat. If all the temp tables have roughly the same name then you would want to look into rewriting the dynamic sql that is creating them as a regular stored procedure or using sp_executesql to make it a parameterized call.
January 2, 2009 at 9:21 am
David Levy (1/2/2009)
I should have added that while freeing the proc cache will clear the symptoms you are seeing temporarily the root cause is likely proc cache bloat. If all the temp tables have roughly the same name then you would want to look into rewriting the dynamic sql that is creating them as a regular stored procedure or using sp_executesql to make it a parameterized call.
this is an ERP program,this ERP program use the sp_executesql to run the queries.
i can't touch any of the syntax that come out from the ERP.
so you say that FREEPROCCAHE can solve my problem temporary?
January 2, 2009 at 9:36 am
Yes, freeing the proccache will bring back the free space in tempdb temporarily. It will not shrink the file for you, but constantly growing and shrinking the file could result in volume fragmentation if you are stuck in a situation where tempdb is not on a dedicated volume.
January 2, 2009 at 12:14 pm
Thx.I'll give it a try when I'll face the tempdb problem again.
Viewing 15 posts - 16 through 30 (of 43 total)
You must be logged in to reply to this topic. Login to reply