17 October 2012

Percentage of used/available database space

by Blaž Dakskobler

23,902 0 Storage

This metric measures the ratio between used database space and total available space, also taking into account current size, maximum size and the amount of free space for each drive. The values collected 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.

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.

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.

Metric definition

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.

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(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
			
				
					
				
					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
				
						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

Instances to collect from

Select the instance that contains the database you’re interested in.

Databases to collect from

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

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 threshholds

Default threshold values

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

1 collection

Alert is

Enabled

23,902 Rate

Click to rate this post!

[Total: 10 Average: 4.7]

Percentage of used/available database space

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