TempDB previous usage

  • Sicilian-Najdorf

    Grasshopper

    Points: 14

    Hi All
    To track down what is causing TempDB to fill up now is something that is done often so that's pretty straight forward.
    Can anyone assist me in finding out how to track down which queries filled up or used TempDB significantly in the past? 
    Will the plan cache be of assistance here? If so, how would I get this kind of information?

    Thanks

  • muthukkumaran Kaliyamoorthy

    SSCoach

    Points: 19023

    Sicilian-Najdorf - Thursday, December 6, 2018 1:29 AM

    Hi All
    To track down what is causing TempDB to fill up now is something that is done often so that's pretty straight forward.
    Can anyone assist me in finding out how to track down which queries filled up or used TempDB significantly in the past? 
    Will the plan cache be of assistance here? If so, how would I get this kind of information?

    Thanks

    You can check and try this https://www.sqlserverblogforum.com/dba/tempdb-database-is-full/

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • Super Cat

    SSCertifiable

    Points: 7314

    SELECT TE.name AS [EventName] ,
       T.DatabaseName ,
       t.DatabaseID ,
       t.NTDomainName ,
       t.ApplicationName ,
       t.LoginName ,
       t.SPID ,
       t.Duration ,
       t.StartTime ,
       t.EndTime,
       planhandle,
       sqlhandle
      
    FROM  sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1
                         f.[value]
                       FROM  sys.fn_trace_getinfo(NULL) f
                      WHERE f.property = 2
                      )), DEFAULT) T
       JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
    WHERE databasename = 'tempdb' and te.name = 'Data File Auto Grow'
       OR te.name = 'Data File Auto Shrink'
            
    ORDER BY t.StartTime ;

    Use the default Trace.

  • Jeff Moden

    SSC Guru

    Points: 995619

    I find that if it's actually to be found in the default trace, you got damned lucky.  Most default traces get full and rollover very quickly on busy systems.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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