How to remove temporary tables in tempdb database

  • 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

  • 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 😀

  • 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

  • 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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply