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;

 

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, speak frequently at conferences, and write at SQLPerformance and MSSQLTips. I recently joined the team at Wayfair and hope to blog about some of the scale challenges I've already started helping to solve.

15 Responses

  1. oracledba says:

    Good stuff.

  2. Ray says:

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

  3. Eoin says:

    Excellent stuff

  4. SQLRocker says:

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

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

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

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

  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. Massimo says:

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

  10. Ganesh says:

    Great script. Thanks Aaron and Martin.

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

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

  13. 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?

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

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