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</p>

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