{"name":"Database growth steps available","description":"<p>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:<\/p>\r\n<ul style=\"list-style-type: disc; padding-left: 2em;\">\r\n<li>database file\u2019s current size<\/li>\r\n<li>database file\u2019s max size<\/li>\r\n<li>growth info (is growth allowed, is growth unlimited, is growth flat or percentage based, growth step size in MB or percent)<\/li>\r\n<li>free space for each drive where database files are located<\/li>\r\n<\/ul>\r\n<p>The final number of available steps is the sum of available steps for each database data file.<\/p>\r\n","tsql":"DECLARE @DriveSpaceFree TABLE (\r\n   Drive CHAR(1)\r\n  ,FreeMB INT);\r\n\r\nINSERT INTO @DriveSpaceFree\r\n   EXEC xp_fixeddrives\r\n\r\nSELECT\r\n   SUM(FileGrowthStepsAvailable) AS SumSteps\r\nFROM\r\n   (SELECT\r\n      CASE WHEN IsGrowthAllowed = 0 THEN 0 -- no growth is allowed => no steps available\r\n           WHEN FileMaxSize = -1 -- unlimited growth => space available is limited by free space on drive\r\n                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\r\n                    AND FreeOnDrive < (FileMaxSize - CurrentFileSize))\r\n           THEN CASE IsPercentGrowth\r\n                  WHEN 0 THEN CAST(FreeOnDrive \/ FileGrowthStepMB AS INT)\r\n                  ELSE CAST(LOG((FreeOnDrive \/ CurrentFileSize) + 1) \/ LOG(1 + (FileGrowthStepPct \/ 100.)) AS INT)\r\n                END\r\n           ELSE -- limited growth, space available is limited by max file size\r\n                CASE IsPercentGrowth\r\n                  WHEN 0 THEN CAST((FileMaxSize - CurrentFileSize) \/ FileGrowthStepMB AS INT)\r\n                  ELSE CAST(LOG(FileMaxSize \/ CurrentFileSize) \/ LOG(1 + (FileGrowthStepPct \/ 100.)) AS INT)\r\n                END\r\n      END AS FileGrowthStepsAvailable\r\n    FROM\r\n      (SELECT\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        ,CASE WHEN df.max_size = 0 THEN 0\r\n              ELSE 1\r\n         END AS IsGrowthAllowed\r\n        ,dsf.FreeMB AS FreeOnDrive\r\n        ,df.growth \/ 128. AS FileGrowthStepMB\r\n        ,df.growth AS FileGrowthStepPct\r\n        ,df.is_percent_growth AS IsPercentGrowth\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 InforPerFile) AS t","instances":false,"frequency":300,"databases":{"mode":1},"rateofchange":false,"metricenabled":true,"alertname":"Database growth steps available low","alertdescription":"Raised when the number of available autogrowth steps gets too low.","aboveorbelow":"below","collections":1,"alertenabled":true,"_thresholds_high":{"selected":true,"value":1},"_thresholds_medium":{"selected":true,"value":3},"_thresholds_low":{"selected":true,"value":5},"thresholds_comment":"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.","collections_comment":"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).","targetentitytype":0}