{"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.\r\n\r\nThe 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.\r\n\r\nThe 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.","tsql":"DECLARE @DriveSpaceFree TABLE (Drive CHAR(1),FreeMB INT);\r\n\r\nINSERT   INTO @DriveSpaceFree\r\n         EXEC xp_fixeddrives\r\n\r\nSELECT\r\n   (SUM(SpaceUsedInFiles) \/ SUM(TotalAvailableSpaceForFiles)) * 100 AS PctFilled\r\nFROM\r\n   (SELECT\r\n      Drive\r\n     ,SUM(FileSpaceUsed) AS SpaceUsedInFiles\r\n     ,CASE WHEN MIN(FileMaxSize) = -1 -- unlimited growth => space available is limited by free space on drive\r\n                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\r\n                    AND MAX(FreeOnDrive) < SUM(FileMaxSize - CurrentFileSize)) THEN SUM(CurrentFileSize) + MAX(FreeOnDrive)\r\n           ELSE SUM(FileMaxSize)\r\n      END AS TotalAvailableSpaceForFiles\r\n    FROM\r\n      (SELECT\r\n         FILEPROPERTY(df.name,'SpaceUsed') \/ 128. AS FileSpaceUsed\r\n        ,df.size \/ 128. AS CurrentFileSize\r\n        ,CASE WHEN df.max_size > 0 THEN df.max_size \/ 128.\r\n              WHEN df.max_size < 0 THEN -1 -- unlimited growth\r\n              ELSE df.size \/ 128. -- max_size=0 => no growth is allowed => FileMaxSize = CurrentFileSize\r\n         END AS FileMaxSize\r\n        ,dsf.Drive\r\n        ,dsf.FreeMB AS FreeOnDrive\r\n       FROM\r\n         sys.database_files df\r\n         JOIN @DriveSpaceFree dsf\r\n            ON LEFT(df.physical_name,1) = dsf.Drive\r\n       WHERE\r\n         df.type = 0 \/* rows data *\/) AS InfoPerFile\r\n    GROUP BY\r\n      Drive) AS InfoPerDrive","instances":false,"frequency":300,"databases":{"mode":0},"rateofchange":false,"metricenabled":true,"alertname":"Used\/available database space increased","alertdescription":"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.\r\n\r\nThe 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.\r\n\r\nIt 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. ","aboveorbelow":"above","collections":1,"alertenabled":true,"_thresholds_high":{"selected":true,"value":90},"_thresholds_medium":{"selected":true,"value":80},"_thresholds_low":{"selected":true,"value":70},"thresholds_comment":"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. ","targetentitytype":0}