Using SQL Server’s Default Trace to Identify Autogrow Events in tempdb

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);

-- 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
, AS EventName
,DB_NAME(ftg.databaseid) AS DatabaseName  
,(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;

See all articles by Greg Larsen

Gregory Larsen
Gregory Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles