Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to remove temporary tables in tempdb database Expand / Collapse
Author
Message
Posted Monday, August 11, 2014 7:45 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, September 21, 2014 9:50 PM
Points: 386, Visits: 2,356
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
Post #1602125
Posted Monday, August 11, 2014 8:26 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 13, 2014 2:22 AM
Points: 130, Visits: 909
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 ) You wont have permission to drop them.


--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully
Post #1602133
Posted Monday, August 11, 2014 8:38 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, September 21, 2014 9:50 PM
Points: 386, Visits: 2,356
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
Post #1602134
Posted Tuesday, August 12, 2014 2:41 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:08 AM
Points: 40,207, Visits: 36,615
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 2008, MVP
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

Post #1602177
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse