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