August 11, 2014 at 7:45 pm
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
August 11, 2014 at 8:26 pm
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 😀
August 11, 2014 at 8:38 pm
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
August 12, 2014 at 2:41 am
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply