TempDB previous usage

• Sicilian-Najdorf

  Grasshopper

  Points: 24

  More actions

  December 6, 2018 at 1:29 am

  #373230

  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: 19026

  More actions

  December 6, 2018 at 4:30 am

  #2015641

  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: 7332

  More actions

  December 6, 2018 at 4:40 am

  #2015642

  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: 1004312

  More actions

  December 6, 2018 at 7:51 am

  #2015660

  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.

  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)