Measures the number of database autogrowth events (data file or log file) in the last hour. Too many autogrowth events causes disk fragmentation which requires a change in the autogrowth settings of a database.<\/p>\r\n
To analyze further, execute the following query to find the growth rate and growth duration for each database over the last 24 hours:<\/p>\r\n
\/*Get the name of the current default trace file*\/\r\nDECLARE @filename NVARCHAR(1000)\r\n\r\nSELECT @filename = CAST(value AS NVARCHAR(1000))\r\nFROM sys.fn_trace_getinfo(DEFAULT)\r\nWHERE traceid = 1 AND property = 2\r\n\r\n\/*separate file name into pieces*\/\r\nDECLARE @bc INT,\r\n@ec INT,\r\n@bfn VARCHAR(1000),\r\n@efn VARCHAR(10)\r\n\r\nSET @filename = REVERSE(@filename)\r\nSET @bc = CHARINDEX('.',@filename)\r\nSET @ec = CHARINDEX('_',@filename)+1\r\nSET @efn = REVERSE(SUBSTRING(@filename,1,@bc))\r\nSET @bfn = REVERSE(SUBSTRING(@filename,@ec,LEN(@filename)))\r\n\r\n\/*set filename without rollover number*\/\r\nSET @filename = @bfn + @efn\r\n\r\n\/*process all trace files and insert data into DB_AutoGrow_Log*\/\r\nSELECT ftg.StartTime,\r\nte.name 'EventName',\r\nDB_NAME(ftg.databaseid) 'DatabaseName',\r\nftg.[Filename] 'FileName',\r\n(ftg.IntegerData*8)\/1024.0 'GrowthMB',\r\n(ftg.duration)\/1000000.0 'Duration_Secs'\r\nFROM fn_trace_gettable(@filename, DEFAULT) AS ftg INNER JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id\r\nWHERE (ftg.EventClass = 92 OR ftg.EventClass = 93) -- Date File Auto-grow, Log File Auto-grow\r\nAND DatabaseID = DB_ID()\r\nAND ftg.StartTime > DATEADD(dd, -1, GETDATE())<\/pre>","tsql":"\/*Get the name of the current default trace file*\/ DECLARE @filename NVARCHAR(1000)\r\n\r\nSELECT @filename = CAST(value AS NVARCHAR(1000)) FROM sys.fn_trace_getinfo(DEFAULT) WHERE traceid = 1 AND property = 2\r\n\r\n\r\n\/*separate file name into pieces*\/\r\nDECLARE @bc INT,\r\n @ec INT,\r\n @bfn VARCHAR(1000),\r\n @efn VARCHAR(10)\r\n\r\nSET @filename = REVERSE(@filename)\r\nSET @bc = CHARINDEX('.',@filename)\r\nSET @ec = CHARINDEX('_',@filename)+1\r\nSET @efn = REVERSE(SUBSTRING(@filename,1,@bc))\r\nSET @bfn = REVERSE(SUBSTRING(@filename,@ec,LEN(@filename)))\r\n\r\n\/*set filename without rollover number*\/ SET @filename = @bfn + @efn\r\n\r\n\r\n\r\n\/*\r\nget the number of autogrow events in the last hour get the max of data file and log file autogrowths *\/ SELECT MAX(Count_AutoGrow) FROM (\r\n SELECT COUNT(*) 'Count_AutoGrow'\r\n FROM fn_trace_gettable(@filename, DEFAULT)\r\n WHERE (EventClass = 92 OR EventClass = 93) -- Date File Auto-grow, Log File Auto-grow\r\n AND StartTime > DATEADD(hh, -1, GETDATE())\r\n AND DatabaseID = DB_ID()\r\n GROUP BY DB_NAME(databaseid), [Filename]\r\n) a\r\n","instances":true,"frequency":3600,"databases":{"mode":0},"rateofchange":false,"metricenabled":true,"alertname":"Database autogrowth","alertdescription":"This alert is raised when the number of autogrowth events that occur on the data file or log file of a database goes above the defined thresholds.","aboveorbelow":"above","collections":1,"alertenabled":true,"_thresholds_high":{"selected":true,"value":10},"_thresholds_medium":{"selected":true,"value":5},"_thresholds_low":{"selected":true,"value":2},"thresholds_comment":"Note: These thresholds are intended as guideline values. If they seem too high or too low, replace them with values more suited to your environment.","targetentitytype":0}