Post reply

Any way to find the name given to a session\'s local temp table?

  • February 24, 2020 at 11:13 pm

    #3728417

    Someone made a change to the code base, inserting a bunch of records to a local temp table and, in turn, blowing up tempdb by 600 GB in a couple hours. No one seems to know exactly what change is responsible for it, so they've asked me to find the name of the session-specific temp table. They all show up as sequential names in tempdb - is there even a way to reverse lookup the name given to the temp table?

  • February 25, 2020 at 4:18 pm

    #3728645

    because temp tables are transient you will really have to catch this exactly as it creates the problem.

    I might think about monitoring tempdb for any table over 5GB (a guess) and then trying to find the SPID... once you have that then you can use either dbcc inputbuffer or sysprocesses to capture the process

    You could just run a job/loop to check for the biggest temp table, but I wonder if there is an XE that you can use... it bet Grant Fritchey will have an answer for you within minutes, he is the king of XE

    MVDBA

Viewing 2 posts - 1 through 1 (of 1 total)

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