tempdb - lots of old local temp tables still hanging around, some ove 30 days old???


  • I was having an issue with a query that uses temp tables (which I tend to stay away from).  I rewrote the procedure using a CTE and it works much faster.  However, I still need to analyze why the older procedure is bogging down, as there is a 3rd party system that uses similar procedures with temp tables.  

    So I started to investigate tempdb for issues

    - tempdb is not full
    - server memory not an issue
    - far too many local #temp tables in tempdb, some over 30 days old ???

    select * from tempdb.sys.objects where type='U' and name like '#%'
    ORDER BY modify_date DESC

    Im guessing that whatever procedures or statements that were generating those local #temp tables, they might have ran into an error, and the temp tables were not dropped?  I thought that local temp tables got dropped after the statement or procedure completed or went out of scope???

    Wednesday, December 4, 2013 5:25 PM


All replies