We all know that you should try to size tempdb appropriately, so it doesn’t need to autogrow shortly after starting up SQL Server. It isn’t always easy to do this. Therefore, when you first implement a new server and/or add new databases you should monitor the autogrowth events on tempdb. By monitoring the autogrowth events you can easily determine if you have sized tempdb appropriately.
If you have the default trace enabled for your server, you can use the script below to identify all your tempdb autogrowth events. Note the default trace is enabled by default. If you run this code and it returns any autogrowth events, then you might want to re-establish the initial size of tempdb to keep these autogrowth events from occurring.
-- Declare variables DECLARE @filename NVARCHAR(1000); DECLARE @bc INT; DECLARE @ec INT; DECLARE @bfn VARCHAR(1000); DECLARE @efn VARCHAR(10); -- Get the name of the current default trace SELECT @filename = CAST(value AS NVARCHAR(1000)) FROM ::fn_trace_getinfo(DEFAULT) WHERE traceid = 1 AND property = 2; -- rip apart file name into pieces SET @filename = REVERSE(@filename); SET @bc = CHARINDEX('.',@filename); SET @ec = CHARINDEX('_',@filename)+1; SET @efn = REVERSE(SUBSTRING(@filename,1,@bc)); SET @bfn = REVERSE(SUBSTRING(@filename,@ec,LEN(@filename))); -- set filename without rollover number SET @filename = @bfn + @efn -- process all trace files SELECT ftg.StartTime ,te.name AS EventName ,DB_NAME(ftg.databaseid) AS DatabaseName ,ftg.Filename ,(ftg.IntegerData*8)/1024.0 AS GrowthMB ,(ftg.duration/1000)AS DurMS FROM ::fn_trace_gettable(@filename, DEFAULT) AS ftg INNER JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id WHERE (ftg.EventClass = 92 -- Date File Auto-grow OR ftg.EventClass = 93) -- Log File Auto-grow AND DB_NAME(ftg.databaseid) = 'tempdb' AND ftg.Starttime > (SELECT login_time FROM sys.dm_exec_sessions WHERE session_id = 1) ORDER BY ftg.StartTime;
