Database autogrowth

| 4 Comments | 8,563 views
Categories:

VN:F [1.9.17_1161]
Rating: 5.0/5 (3 votes cast)

This metric 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.

To analyze further, execute the following query to find the growth rate and growth duration for each database over the last 24 hours:

/*Get the name of the current default trace file*/
DECLARE @filename NVARCHAR(1000)

SELECT @filename = CAST(value AS NVARCHAR(1000))
FROM sys.fn_trace_getinfo(DEFAULT)
WHERE traceid = 1 AND property = 2

/*separate file name into pieces*/
DECLARE @bc INT,
@ec INT,
@bfn VARCHAR(1000),
@efn VARCHAR(10)

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 and insert data into DB_AutoGrow_Log*/
SELECT ftg.StartTime,
te.name 'EventName',
DB_NAME(ftg.databaseid) 'DatabaseName',
ftg.[Filename] 'FileName',
(ftg.IntegerData*8)/1024.0 'GrowthMB',
(ftg.duration)/1000000.0 'Duration_Secs'
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 OR ftg.EventClass = 93) -- Date File Auto-grow, Log File Auto-grow
AND DatabaseID = DB_ID()
AND ftg.StartTime > DATEADD(dd, -1, GETDATE())

For more information, see SQL Server Database Growth and Autogrowth Settings.

Database autogrowth, 5.0 out of 5 based on 3 ratings

Metric definition

Metric Name:
Database autogrowth
Description:

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.

To analyze further, execute the following query to find the growth rate and growth duration for each database over the last 24 hours:

/*Get the name of the current default trace file*/
DECLARE @filename NVARCHAR(1000)

SELECT @filename = CAST(value AS NVARCHAR(1000))
FROM sys.fn_trace_getinfo(DEFAULT)
WHERE traceid = 1 AND property = 2

/*separate file name into pieces*/
DECLARE @bc INT,
@ec INT,
@bfn VARCHAR(1000),
@efn VARCHAR(10)

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 and insert data into DB_AutoGrow_Log*/
SELECT ftg.StartTime,
te.name 'EventName',
DB_NAME(ftg.databaseid) 'DatabaseName',
ftg.[Filename] 'FileName',
(ftg.IntegerData*8)/1024.0 'GrowthMB',
(ftg.duration)/1000000.0 'Duration_Secs'
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 OR ftg.EventClass = 93) -- Date File Auto-grow, Log File Auto-grow
AND DatabaseID = DB_ID()
AND ftg.StartTime > DATEADD(dd, -1, GETDATE())
Enter the T-SQL query that will collect data:
/*Get the name of the current default trace file*/ DECLARE @filename NVARCHAR(1000)

SELECT @filename = CAST(value AS NVARCHAR(1000)) FROM sys.fn_trace_getinfo(DEFAULT) WHERE traceid = 1 AND property = 2


/*separate file name into pieces*/
DECLARE @bc INT,
                @ec INT,
                @bfn VARCHAR(1000),
                @efn VARCHAR(10)

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



/*
get the number of autogrow events in the last hour get the max of data file and log file autogrowths */ SELECT MAX(Count_AutoGrow) FROM (
        SELECT  COUNT(*) 'Count_AutoGrow'
        FROM fn_trace_gettable(@filename, DEFAULT)
        WHERE   (EventClass = 92 OR EventClass = 93) -- Date File Auto-grow, Log File Auto-grow
                                AND StartTime > DATEADD(hh, -1, GETDATE())
                                AND DatabaseID = DB_ID()
        GROUP BY DB_NAME(databaseid), [Filename]
) a
Select instances to collect from:
Select all
Choose databases to collect from:
All databases
Set collection frequency:
Collect data every hour
Use collected or calculated values:
Leave the Use a calculated rate of change between collections check box turned off
Metric collection is:
Enabled

Alert definition

Alert name:
Database autogrowth
Description:
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.
Raise an alert when the metric value goes:
Above the defined thresholds
Default threshold values:
Set the thresholds as follows:
High:10
Medium:5
Low:2
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.
Raise an alert when the threshold is passed for:
Enter 1 collection
This alert is:
Enabled
VN:F [1.9.17_1161]
Rating: 5.0/5 (3 votes cast)
  • Mark Steenbakkers

    Hi!

    This script seems to work great on almost all of my databases (600+) in SQLMonitor, but on some of them I get the following error:

    Msg 567, Level 16, State 5, Line 24

    File ” either does not exist or is not a recognizable trace file. Or there was an error opening the file.

    Any idea what might cause this?

    Thanks

    Mark

    VA:F [1.9.17_1161]
    Rating: 0 (from 0 votes)
    • Riken Patel

      Hi,

      I think this might be that your trace file does not exist. They are normally in the log folder in the SQL Server Program Files path C:Program FilesMicrosoft SQL Server…MSSQLLog and will be like “log_???.trc”

      VA:F [1.9.17_1161]
      Rating: 0 (from 0 votes)
      • Mark Steenbakkers

        Hi Riken,

        Found the problem; the file(s) did exist but they were corrupted somehow. I disabled the default trace, deleted the old tracefiles and enabled the default trace again. Problem solved.

        Now to find out how the files became corrupted in the first place.

        Thanks for your answer!

        Regards,
        Mark

        VA:F [1.9.17_1161]
        Rating: 0 (from 0 votes)
  • Nico Botes

    Great, thanks! I am going to use this one, and already have a SQL Server Agent Alert and a custom solution of saving the “black-box”-trace data in a table. That should cover all bases I hope… :)

    Not sure if it is worth mentioning and adding this to the custom metric description, that one can also use the Report in “SSMS –> Standard Reports –> Disk Usage” for further investigations. The entries should appear under the pie-graphs on the report.

    VA:F [1.9.17_1161]
    Rating: 0 (from 0 votes)