Extened Events to find queries using tempdb log space

• webrunner

  SSC-Dedicated

  Points: 31715

  More actions

  August 19, 2016 at 12:00 pm

  #320774

  Hello experts,

  I saw this great article on Brent Ozar's site:

  https://www.brentozar.com/archive/2015/12/tracking-tempdb-growth-using-extended-events/[/url]

  There is a wrinkle for me, though. That article captures tempdb file growth and file shrinking. I want to capture increases in tempdb log space usage and which queries are causing that space usage.

  I used another helpful link to track increases in tempdb log space usage.

  http://sqlmonitormetrics.red-gate.com/percentage-of-free-log-space/[/url]

  But although I got an alert from that custom metric that the percent of log space used had increased, the Extended Events session columns 'size_change_kb' was blank for all rows, and I think I know why. The space usage did not cause the tempdb data or log files to grow.

  Does anyone know how I can modify an Extended Events session to capture a particular large increase in log space usage (say, 50% or more) with a list of which running queries are demanding that space from tempdb -- even if the file as a whole did not increase in size?

  Thanks for any help!

  - webrunner

  -------------------
  A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
  Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html