Percentage of used/available database space

| 3 Comments | 11,617 views
Categories:

VN:F [1.9.17_1161]
Rating: 4.7/5 (9 votes cast)

This metric measures the ratio between used database space and total available space, taking into account the following database file metrics:

  • current used space
  • current size
  • maximum size
  • free space for each drive where database files are located

The actual available space per drive is calculated first, followed by the sum of the total used space from drives and the total available space from drives. These values are then used to calculate the global percentage.

The metric helps you to maintain control over the database storage area, and monitor database growth in combination with general space management on a database file’s host. If available space decreases or increases because something else in your environment, such as another database, starts or stops taking up space, you may want to analyze this further. You are also alerted when the percentage increases signficantly.

Percentage of used/available database space, 4.7 out of 5 based on 9 ratings

Metric definition

Metric Name:
Percentage of used/available database space
Description:
This metric measures the ratio between used database space and total available space, taking into account the database file's current used space, current size, maximum size, and free space for each drive where database files are located. The actual available space per drive is calculated first, followed by the sum of the total used space from drives and the total available space from drives. These values are then used to calculate the global percentage. The metric helps you to help you maintain control over the database storage area, and monitor database growth in combination with general space management on a database file's host. If available space decreases or increases because something else in your environment, such as another database, starts or stops taking up space, you may want to analyze this further. You are also alerted when the percentage increases signficantly.
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(SpaceUsedInFiles) / SUM(TotalAvailableSpaceForFiles)) * 100 AS PctFilled
FROM
   (SELECT
      Drive
     ,SUM(FileSpaceUsed) AS SpaceUsedInFiles
     ,CASE WHEN MIN(FileMaxSize) = -1 -- unlimited growth => space available is limited by free space on drive
                OR (MIN(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 MAX(FreeOnDrive) < SUM(FileMaxSize - CurrentFileSize)) THEN SUM(CurrentFileSize) + MAX(FreeOnDrive)
           ELSE SUM(FileMaxSize)
      END AS TotalAvailableSpaceForFiles
    FROM
      (SELECT
         FILEPROPERTY(df.name,'SpaceUsed') / 128. AS FileSpaceUsed
        ,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
        ,dsf.Drive
        ,dsf.FreeMB AS FreeOnDrive
       FROM
         sys.database_files df
         JOIN @DriveSpaceFree dsf
            ON LEFT(df.physical_name,1) = dsf.Drive
       WHERE
         df.type = 0 /* rows data */) AS InfoPerFile
    GROUP BY
      Drive) AS InfoPerDrive
Select instances to collect from:
Select the instance that contains the database you’re interested in.
Choose databases to collect from:
All databases
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:
Used/available database space increased
Description:
Raised when the fill percentage exceeds a specified threshold. The metric on which this alert is based measures the ratio between used database space and total available space, taking into account the database file's current used space, current size, maximum size, and free space for each drive where database files are located. The actual available space per drive is calculated first, followed by the sum of the total used space from drives and the total available space from drives. Those values are then used to calculate the global percentage. It is best practice to monitor database growth in combination with general space management on a database file's host. If available space decreases or increases because something else in your environment (for example, another database) starts or stops taking up space, you may want to analyze this further.
Raise an alert when the metric value goes:
Above the defined thresholds
Default threshold values:
Set the thresholds as follows:
High:90
Medium:80
Low:70
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 server performance.
Raise an alert when the threshold is passed for:
Enter 1 collection
This alert is:
Enabled
VN:F [1.9.17_1161]
Rating: 4.7/5 (9 votes cast)
  • Pingback: SQL Monitor metrics competition – the winners! | SQL Monitor Metrics()

  • Velvet

    Thank you! This is PERFECT for my needs!

    VA:F [1.9.17_1161]
    Rating: 0 (from 0 votes)
  • Jose Vázquez

    I think that there is something missing here: mountpoints are not taken into consideration. What do youn think about using the dm_os_volume_stats DMV ? It shows mountpoints.

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