tempdb Growth
-
Hi, So I am at a loss and would appreciate any insight. I have SQL Server 2012 ENT edition running (Clustered). The tempdb resides on 200GB of SSD. Now this system has 35 user databases on it for all different sorts of systems. The tempdb grows every night by 4-5GB but the space is never released so the temp db just keeps getting bigger and bigger. At the last reboot of the server it was 144GB.
There are no jobs that run at that time, so I can rule jobs out as the cause. Any idea as to how to try and catch what the culprit might be ?
Thanks
Hope this helps...
Ford Fairlane
Rock and Roll Detective
-
Extended Events would be a good choice here, using EVENT sqlserver.database_file_size_change . Just plug in your Database ID (predicate section).
I went through the same issue as and was shocked to see the results (when I got them) !
-
Also check if any open transactions are running when tempdb is growing. Also check if data or log files are growing to narrowdown the issue.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't." -
Depending on the amount of activity, you can also get some information from the default trace (session ID, application name, login name, and such). If your server's especially busy, the default trace may have already cycled through, but it's a nice way to get some information without setting up anything new.
DECLARE @filename NVARCHAR(4000);-- Current default trace
SELECT @filename = CAST(value AS NVARCHAR(4000))
FROM ::
FN_TRACE_GETINFO(DEFAULT)
WHERE traceid = 1
AND property = 2
-- Preserve the path and replace the current default trace with log.trc
SET @filename = LEFT(@filename,
LEN(@filename) - CHARINDEX('\',REVERSE(@filename)))
+ '\log.trc'
-- Auto growth events in the current trace file
SELECT
TE.name AS [EventName],
T.DatabaseName,
t.DatabaseID,
t.NTDomainName,
t.ApplicationName,
t.LoginName,
t.SPID,
t.Duration,
t.StartTime,
t.EndTime,
t.textdata
FROM sys.fn_trace_gettable ( @filename, DEFAULT) T
JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
WHERE TE.name IN ('Data File Auto Grow','Log File Auto Grow')
ORDER BY t.StartTime desc;
Cheers!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply