January 11, 2007 | SQL Server

Reviewing AutoGrow events from the default trace

Since I don't have the luxury of setting up event notifications on all my servers, in SQL Server 2005 I can use the default trace to monitor autogrow events… this helps to prepare for increased disk space usage, and also lets me know if my log backups are happening frequently enough.

This is probably covered in a ton of other places, but the question comes up enough that I thought I would add my quick & dirty methodology. Here is a quick method that grabs the folder for the default trace from sys.traces, then passes that folder into sys.fn_trace_gettable.

DECLARE @path NVARCHAR(260);
 
SELECT 
   @path = REVERSE(SUBSTRING(REVERSE([path]), 
   CHARINDEX('\', REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;
 
SELECT 
   DatabaseName,
   [FileName],
   SPID,
   Duration,
   StartTime,
   EndTime,
   FileType = CASE EventClass 
       WHEN 92 THEN 'Data'
       WHEN 93 THEN 'Log'
   END
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE
   EventClass IN (92,93)
ORDER BY
   StartTime DESC;

 

15 comments on this post

    • oracledba - June 2, 2008, 6:53 PM

      Good stuff.

    • Ray - August 6, 2008, 12:48 AM

      Great script! Thank you.
      I did need to make one minor change. Had to enclose the path with double-quotes.

    • Eoin - October 9, 2009, 3:38 PM

      Excellent stuff

    • SQLRocker - August 5, 2010, 10:28 PM

      Right Click Database-Reports-Data Usage-Data/Log Files Autogrow/Autoshrink Events. Script is good for verification.

    • Martin - July 21, 2011, 5:06 PM

      You don't need to use xp_cmdshell for this. You can use
      DECLARE @path NVARCHAR(260)
      SELECT @path = REVERSE(SUBSTRING(REVERSE(path), CHARINDEX('\', REVERSE(path)), 260)) + N'log.trc'
      FROM    sys.traces
      WHERE   is_default = 1
      SELECT COUNT(*)
      FROM sys.fn_trace_gettable(@path, DEFAULT)

    • AaronBertrand - July 21, 2011, 5:21 PM

      Thanks Martin. In fact the code sample I gave uses DEFAULT as the second parameter and this leads to counting events up to n times (where n is the number of trace files) depending on which file the event occurs in. I've corrected that.

    • opc.three - October 24, 2011, 10:15 PM

      Agreed. No need to use xp_CmdShell. Thanks Aaron for the initial solution and idea. Thanks Martin, I have removed the use of xp_CmdShell from my monitor process, always a welcome chore.

    • Garry Bargsley - March 30, 2012, 11:06 PM

      Is there a way to see what size the TempDB grew by?  The settings in the SSMS GUI do not match the setting I get returned from sp_helpfile.  I am trying to figure out which one is being used when autogrow kicks in.

    • Massimo - December 5, 2012, 2:10 PM

      You can add the column IntegerData/128
      It says the size (in MB) the TempDB grew by

    • Ganesh - December 14, 2012, 6:54 PM

      Great script. Thanks Aaron and Martin.

    • Wayne - May 21, 2015, 6:30 PM

      I just read in SQL 2014 BOL that fn_trace_gettable will be removed in a future edition.  Since they didn't say what the alternative was, I'm hoping it isn't deprecated right now.

    • AaronBertrand - May 26, 2015, 7:31 PM

      @Wayne no, you can still use it. "Deprecated" does not mean "no longer exists and can't be used" it just means that at some point in a later version it will be phased out.

    • Shivendra Kumar Yadav - September 3, 2015, 4:59 PM

      Nice! thanks for sharing.
      I have a doubt that It gets clear frequently so what is the frequency of it.
      Can I get information of a day or something?

    • AaronBertrand - September 14, 2015, 7:37 PM

      @Shivendra There is no way for me to know how long information stays in your default trace – it all depends on how much activity you have that is captured by the trace. Can you get information from a specific day? Sure, as long as that information hasn't been rolled out, you can filter on the StartTime/EndTime columns.

    • Shivendra Kumar Yadav - September 15, 2015, 3:44 PM

      Thanks Aaron,
      Yes, I am also experiencing the same that It gets clear any-time.
      I have just set SQL profiler with “Data File Auto grow” and “Log File Auto Grow” events since last 7-8 days and using your query with it for analysis.
      It has not impacted the CPU consumption too. The file size and its open running to the window is my concern now.

Comments are closed.