Reviewing AutoGrow events from the default trace
January 11th, 200715
Reviewing AutoGrow events from the default trace
January 11th, 200715

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.

;WITH p as
  SELECT [path] = REVERSE(SUBSTRING(p, CHARINDEX(N'\', p), 260)) + N'log.trc'
  FROM (SELECT REVERSE([path]) FROM sys.traces WHERE is_default = 1) s(p)
   FileType = CASE t.EventClass 
       WHEN 92 THEN 'Data'
       WHEN 93 THEN 'Log'
FROM p CROSS APPLY sys.fn_trace_gettable(p.[path], DEFAULT) AS t
WHERE t.EventClass IN (92,93)
ORDER BY t.StartTime DESC;

On modern versions, though, you may want to consider replacing the default trace with Extended Events. See my three-part series on that here.

By: Aaron Bertrand

I am a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. I am a long-time Microsoft MVP, write at Simple Talk, SQLPerformance, and MSSQLTips, and have had the honor of speaking at more conferences than I can remember. In non-tech life, I am a husband, a father of two, a huge hockey and football fan, and my pronouns are he/him.

15 Responses

  1. Shivendra Kumar Yadav says:

    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.

  2. AaronBertrand says:

    @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.

  3. Shivendra Kumar Yadav says:

    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?

  4. AaronBertrand says:

    @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.

  5. Wayne says:

    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.

  6. Ganesh says:

    Great script. Thanks Aaron and Martin.

  7. Massimo says:

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

  8. Garry Bargsley says:

    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.

  9. opc.three says:

    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.

  10. AaronBertrand says:

    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.

  11. Martin says:

    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
    FROM sys.fn_trace_gettable(@path, DEFAULT)

  12. SQLRocker says:

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

  13. Eoin says:

    Excellent stuff

  14. Ray says:

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

  15. oracledba says:

    Good stuff.