May 7, 2018 at 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?
May 7, 2018 at 11:36 am
I think you'd need some eventing (XE) to capture tempdb usage and also activity that's going on at that point.
May 7, 2018 at 11:56 am
thanks!
May 7, 2018 at 11:58 am
ericwenger1 - Monday, May 7, 2018 10:05 AMHi,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