Diagnosing what consumed alot of the tempdb during a pont in time

  • Hi,

    We had an issue  this past Friday  where we went from the disk drive containing the tempdb which was 60% free space down to 4% free space in about a five minute window. I'm trying to figure out what was the consumer of  most of the tempdb. We just allocated more space until our maintenance window in which we will reboot the server and the tempdb will get recreated. Any ideas how I can diagnose this?

  • I think you'd need some eventing (XE) to capture tempdb usage and also activity that's going on at that point.

  • thanks!

  • ericwenger1 - Monday, May 7, 2018 10:05 AM

    Hi,

    We had an issue  this past Friday  where we went from the disk drive containing the tempdb which was 60% free space down to 4% free space in about a five minute window. I'm trying to figure out what was the consumer of  most of the tempdb. We just allocated more space until our maintenance window in which we will reboot the server and the tempdb will get recreated. Any ideas how I can diagnose this?

    If you had nothing set up in advance, most activity is difficult to track or not possible. One thing is you can try to query the trace files and see if there are any autogrow events for tempdb. From there you can check what was going on around that time (any maintenance) and you might be able to get an idea of what led to the growth

    DECLARE @path nvarchar(500);

    SELECT @path =
        REVERSE(SUBSTRING(REVERSE([path]),
        CHARINDEX(CHAR(92), REVERSE([path])), 500)) + N'log.trc'
    FROM sys.traces
    WHERE is_default = 1;

    SELECT
        te.name as EventClassName,
        tr.*
    FROM fn_trace_gettable(@path, default) tr
    inner join sys.trace_events te
    ON tr.eventclass = te.trace_event_id
    WHERE te.name = 'Log File Auto Grow'
    AND DatabaseName = 'tempdb'

    Sue

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

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