====== MS-SQL Autogrowth ====== ===== Purpose ===== SQL Server engine expands the size of a database file when it runs out of space, the amount by which a database file grows is based on settings. It is then very important to monitor files growth size, their percentage size within the disk size, the remaining free disk space after the expansion. **Monitor features include :** * Monitoring of every single database file growth (in MB or in percentage values) * Monitoring of every single database file remaining disk space after growing (in MB or in percentage values) * It can also detect and alert whether a given file will automatically grow or not

===== Configuration hints ===== **Surveillance table:** * This is a table of rules that you can use to configure and customize the configuration. * Each line of the table will define a monitoring rule. You can combine multiple rules to cover different cases. * Within a rule, you can configure the **database file types** (data, log, etc.) you want to monitor, a **given file name (or file name pattern)**, a **growth size threshold**, a **remaining free disk space after file growing threshold** * You can also set a severity level of the**alert to be sent if there is no enough free disk space for the database file to grow** * It is possible to set a severity level for the**alert to be sent if a given database file will not automatically grow** **File type**\\ Select the specific file type (data, log) you want to monitor or select "All" to monitor any kind of file **Growth size:**\\ Use the multi-threshold syntax to set multiple threshold/severity associations: G2W:10 W2M:20 M2C:30 (Green To Warning, Warning To Major, etc...), expressed in MB or percentage An alarm of the corresponding severity will be sent the size of the file growth is **over** threshold. Set 0 in the field if unused **Remaining space after growth:**\\ Use the multi-threshold syntax to set multiple threshold/severity associations: G2W:30 W2M:20 M2C:10 (Green To Warning, Warning To Major, etc...), expressed in MB or percentages An alarm of the corresponding severity will be sent if the remaining free disk space after the file growing is **under** threshold. Set 0 in the field if unused **Not enough space to grow:**\\ Set the severity level (Warning, Major, Critical, etc...) of the alert you want to send if there is no enough free disk space for a given database file to grow. Set it to "DISABLED" to deactivate the alert **Autogrow not enabled:**\\ Set a alert severity level if a given database file will not grow automatically and you want to be notified. Set it to "DISABLED" to deactivate the alert ===== Surveillance table ===== ^Parameter^Description^ ^Active|Use this field to activate or deactivate a line of configuration.| ^File type|Select the database file type you want to monitor.Select "All" if you want to monitor any file.| ^File name|A filter to match only a specific database file, accepts regular expression, white or black list.| ^Growth size|The threshold for the growth size. You can use the multi threshold syntax (i.e. : G2W:10 W2C:40, expressed in percentage or in MB)| ^Growth size unit|Represents the unit of measure of the growth size threshold, that can be expressed in MB or percentage values.| ^Remaining space after grow threshold|The threshold for the remaining free disk space after the file growing. You can use the multi threshold syntax (i.e. : G2W:30 W2C:10, expressed in percentage or in MB)| ^Remaining after Unit|Represents the unit of measure of the remaining free disk space after file growing threshold, it can be expressed in MB or percentage values.| ^Not enough space to grow|Set a severity level if you want to be alerted if there is no enough space to grow| ^Autogrow not enabled|Set a severity level if you need to be alerted if a given file will **not** automatically grow| ^Auto clear|If checked, the alarm will be cleared as soon as the alarm condition is not met anymore.| ^Alarm tag|This field allows to add custom text within the alarm message. %MSG% variable will contain the actual generated message and can be used such as: "my_prefix %MSG% my_suffix". By default, tag will be used as prefix.| ^Alarm|If checked, this line of surveillance will be used for alarm generation.| ^Metric|If checked, this line of surveillance will be used for metric generation.| ^Report|If checked, this line of surveillance will used for showing threshold and severity in the daily report| === Examples === ^Active^File type^File name^Growth size threshold^Growth size unit^Remaining space after grow threshold^Remaining after Unit^Severity not enough space to grow^Severity autogrow enabled^Auto clear^Alarm tag^Alarm^Metric^Report^ |true|Data|DBDATA|G2W:70 W2M:85|Percent|G2W:30 W2M:10|Percent|critical|warning|true|true|true|true|true| **Effect** : For this rule, only Data files type will be monitored (File Type set to : "Data"). Only files which names contains "DBDATA" string (DBDATA1, DBDATA2, etc) will be taken into account A WARNING alarm is sent if growth size of the database file is 70% or more, a MAJOR alarm if 85% or more. A WARNING alarm is sent if remaining free disk space after database file growing is 30% or less, a MAJOR alarm if 10% or less. A CRITICAL alarm will be sent if there's no enough disk space for the database file to grow A WARNING alarm will be sent if a the database file will not automatically grow === Generated metrics === ^metricId^metricUnit^metricTarget^metricDescription^ |MSSQL_DATABASE_GROWTH_SIZE|MB|[File Type] - [File name]|The fetched growth size value for a given database file, expressed in MB| |MSSQL_DATABASE_GROWTH_SIZE|percentage|[File Type] - [File name]|The fetched growth size value for a given database file, expressed in percentage| |MSSQL_DATABASE_REMAINING_SPACE_AFTER_GROW|MB|[File Type] - [File name]|Calculated remaining space in file after grow, expressed in MB| |MSSQL_DATABASE_REMAINING_SPACE_AFTER_GROW|percentage|[File Type] - [File name]|Calculated remaining space in file after grow, expressed in percentage|