27 February 2013

Database growth steps available

by Blaž Dakskobler

8,826 0 Storage

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.

Install metric...

Metrics install automatically if you have Redgate Monitor installed.

If you are using Redgate’s SQL Server monitoring tool, Redgate Monitor, you can instantly install and run this metric on your servers.

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

Metric definition

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.

The T-SQL query that will collect data


		
			
			
Transact-SQL
			
			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
			
				
					
				
					1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
				
						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

Instances to collect from

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

Databases to collect from

the name of the database you want to monitor

Collection frequency

300

Use collected or calculated values

Leave the Use a calculated rate of change between collections check box unchecked

Metric collection

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 threshholds

Default threshold values

High:	1
Medium:	3
Low:	5

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

1 collection

Alert is

Enabled

8,826 Rate

Click to rate this post!

[Total: 7 Average: 5]

Database growth steps available

Metric definition

Name

Description

The T-SQL query that will collect data

Instances to collect from

Databases to collect from

Collection frequency

Use collected or calculated values

Metric collection

Alert definition

Alert name

Description

Raise an alert when the metric value goes

Default threshold values

Raise an alert when the threshold is passed for

Alert is

What are custom metrics?

What is Redgate Monitor?

Featured custom metrics

Total database file size

Number of active backups and restores

Full backup to odd path

Find metrics