Database growth steps available

February 27, 2013 by Blaž Dakskobler | 0 comments | 1,302 views
Categories: Storage

Rating: 5.0/5 (4 votes cast)

This metric measures the number of available autogrowth steps for a database, so you can see when they are reduced or increased because something else (e.g. another database) starts or stops taking up space, when storage is added, etc.

With appropriate thresholds set on the associated alert, you will be notified when the number of available steps gets too low.

Metric definition

Metric Name:

Database growth steps available

Description:

This metric calculates the number of autogrowth steps available for a database at a given time. It does this from a database file level, taking into account:

database file’s current size
database file’s max size
growth info (is growth allowed, is growth unlimited, is growth flat or percentage based, growth step size in MB or percent)
free space for each drive where database files are located

The final number of available steps is the sum of available steps for each database data file.

Enter the T-SQL query that will collect data:

DECLARE @DriveSpaceFree TABLE (
   Drive CHAR(1)
  ,FreeMB INT);

INSERT INTO @DriveSpaceFree
   EXEC xp_fixeddrives

SELECT
   SUM(FileGrowthStepsAvailable) AS SumSteps
FROM
   (SELECT
      CASE WHEN IsGrowthAllowed = 0 THEN 0 -- no growth is allowed => no steps available
           WHEN FileMaxSize = -1 -- unlimited growth => space available is limited by free space on drive
                OR (FileMaxSize <> -1 -- limited growth, but less space on drive than growth space left in files => space available is limited by free space on drive
                    AND FreeOnDrive < (FileMaxSize - CurrentFileSize))
           THEN CASE IsPercentGrowth
                  WHEN 0 THEN CAST(FreeOnDrive / FileGrowthStepMB AS INT)
                  ELSE CAST(LOG((FreeOnDrive / CurrentFileSize) + 1) / LOG(1 + (FileGrowthStepPct / 100.)) AS INT)
                END
           ELSE -- limited growth, space available is limited by max file size
                CASE IsPercentGrowth
                  WHEN 0 THEN CAST((FileMaxSize - CurrentFileSize) / FileGrowthStepMB AS INT)
                  ELSE CAST(LOG(FileMaxSize / CurrentFileSize) / LOG(1 + (FileGrowthStepPct / 100.)) AS INT)
                END
      END AS FileGrowthStepsAvailable
    FROM
      (SELECT
        df.size / 128. AS CurrentFileSize
        ,CASE WHEN df.max_size > 0 THEN df.max_size / 128.
              WHEN df.max_size < 0 THEN -1 -- unlimited growth
              ELSE df.size / 128. -- max_size=0 => no growth is allowed => FileMaxSize = CurrentFileSize
         END AS FileMaxSize
        ,CASE WHEN df.max_size = 0 THEN 0
              ELSE 1
         END AS IsGrowthAllowed
        ,dsf.FreeMB AS FreeOnDrive
        ,df.growth / 128. AS FileGrowthStepMB
        ,df.growth AS FileGrowthStepPct
        ,df.is_percent_growth AS IsPercentGrowth
       FROM
         sys.database_files df
         JOIN @DriveSpaceFree dsf
            ON LEFT(df.physical_name,1) = dsf.Drive
       WHERE
         df.type = 0 /* rows data */) AS InforPerFile) AS t

Select instances to collect from:

Type the name of the instance containing the database you want to monitor

Choose databases to collect from:

Specify databases and type

the name of the database you want to monitor

Set collection frequency:

Collect data every 5 minutes

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 growth steps available low

Description:

Raised when the number of available autogrowth steps gets too low.

Raise an alert when the metric value goes:

Below the defined thresholds

Default threshold values:

Set the thresholds as follows:

High:	1
Medium:	3
Low:	5

Note: These thresholds are intended as guideline values. If they seem too high or too low for your environment, replace them with values more suited to your environment.

Raise an alert when the threshold is passed for:

Enter 1 collection

Note: Consider increasing this value if temporary reduction of space is common on drives that host your DB files (e.g. someone dumps a backup file on such a drive but (re)moves it in a couple of minutes).

This alert is:

Enabled