dcsimg
 

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

Monday Jul 2nd 2018 by Greg Larsen

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.

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;

See all articles by Greg Larsen

Home
Mobile Site | Full Site
Copyright 2018 © QuinStreet Inc. All Rights Reserved