Is there a way to see when (history / log records) of .mdf / .ldf files grew?
Ultimately, is it possible to setup alerting and know immediately when my database / log files grow?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
Here is a script that I use. I don't remember the source, but it is handy, as it will show you the growth events.
DECLARE @filename NVARCHAR(1000); DECLARE @bc INT; DECLARE @ec INT; DECLARE @bfn VARCHAR(1000); DECLARE @efn VARCHAR(10); -- Get the name of the current default trace SELECT @filename = CAST(value AS NVARCHAR(1000)) FROM ::fn_trace_getinfo(DEFAULT) WHERE traceid = 1 AND property = 2; -- rip apart file name into pieces SET @filename = REVERSE(@filename); SET @bc = CHARINDEX('.',@filename); SET @ec = CHARINDEX('_',@filename)+1; SET @efn = REVERSE(SUBSTRING(@filename,1,@bc)); SET @bfn = REVERSE(SUBSTRING(@filename,@ec,LEN(@filename))); -- set filename without rollover number SET @filename = @bfn + @efn -- process all trace files SELECT ftg.StartTime ,te.name AS EventName ,DB_NAME(ftg.databaseid) AS DatabaseName ,ftg.Filename ,(ftg.IntegerData*8)/1024.0 AS GrowthMB ,(ftg.duration/1000)AS DurMS FROM ::fn_trace_gettable(@filename, DEFAULT) AS ftg INNER JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id WHERE (ftg.EventClass = 92 -- Date File Auto-grow OR ftg.EventClass = 93) -- Log File Auto-grow ORDER BY ftg.StartTime