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