SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to remove temporary tables in tempdb database


How to remove temporary tables in tempdb database

Author
Message
Sri8143
Sri8143
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1163 Visits: 2469
Hi,

There are multiple temp tables with different suffix in the tempdb which i cant drop or delete it as it is from a different session which is not active anymore.. I believe those temp tables are being created as part of a stored procedure which is being executed by sql agent job which is failing half way through so that it is never being dropped ..How to handle or reduce the size of the tempdb.

Yes, we will fix the rootcause but how do we get the data back or get rid of those tables ?

Thanks,
Chinna

Its the Journey which gives you Happiness not the Destination-- Dan Millman
sqlsurfing
sqlsurfing
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1032 Visits: 1190
Sri8143 (8/11/2014)
Hi,

There are multiple temp tables with different suffix in the tempdb which i cant drop or delete it as it is from a different session which is not active anymore.. I believe those temp tables are being created as part of a stored procedure which is being executed by sql agent job which is failing half way through so that it is never being dropped ..How to handle or reduce the size of the tempdb.

Yes, we will fix the rootcause but how do we get the data back or get rid of those tables ?


Are those sessions logged off or just "inactive" on the SQL Server Instance? I believe the temp tables will stay until sessions are closed. (or Killed but might be risky to do w00t ) You wont have permission to drop them.

--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully :-D
Sri8143
Sri8143
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1163 Visits: 2469
Let me explain in more detail..

I do have admin rights on the server.

The temp table is being created by a stored procedure
The stored proc is being executed by a sql agent job
the stored proc keeps failing half way through the code so i believe it never gets to drop the temp tables ..

So when the sql agent job completes or fails will it drop the temp tables ? Also how can i find the session id for the temporray tables in tempdb ? As those temp tables are created from different session which is not active anymore how do i kill it ?

Thanks,
Chinna

Its the Journey which gives you Happiness not the Destination-- Dan Millman
GilaMonster
GilaMonster
SSC Guru
SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)

Group: General Forum Members
Points: 227565 Visits: 46339
The temp table will be dropped as soon as the procedure that created it ends, whether it ends successfully or not does not matter. There's nothing you need to do, the temp tables are cleaned up automatically.

There's no way to identify which session created a temp table and you cannot drop one that you did not create.

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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search