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) ) SELECT t.DatabaseName, t.[FileName], t.SPID, t.Duration, t.StartTime, t.EndTime, FileType = CASE t.EventClass WHEN 92 THEN 'Data' WHEN 93 THEN 'Log' END 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.
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.
@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.
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?
@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.
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.
Great script. Thanks Aaron and Martin.
You can add the column IntegerData/128
It says the size (in MB) the TempDB grew by
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.
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.
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.
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)
Right Click Database-Reports-Data Usage-Data/Log Files Autogrow/Autoshrink Events. Script is good for verification.
Excellent stuff
Great script! Thank you.
I did need to make one minor change. Had to enclose the path with double-quotes.
Good stuff.