products:promonitor:6.8:monitorsguide:mssql:ms-sqlautogrowth

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

ParameterDescription
ActiveUse this field to activate or deactivate a line of configuration.
File typeSelect the database file type you want to monitor.Select “All” if you want to monitor any file.
File nameA filter to match only a specific database file, accepts regular expression, white or black list.
Growth sizeThe 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 unitRepresents the unit of measure of the growth size threshold, that can be expressed in MB or percentage values.
Remaining space after grow thresholdThe 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 UnitRepresents 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 growSet a severity level if you want to be alerted if there is no enough space to grow
Autogrow not enabledSet a severity level if you need to be alerted if a given file will not automatically grow
Auto clearIf checked, the alarm will be cleared as soon as the alarm condition is not met anymore.
Alarm tagThis 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.
AlarmIf checked, this line of surveillance will be used for alarm generation.
MetricIf checked, this line of surveillance will be used for metric generation.
ReportIf checked, this line of surveillance will used for showing threshold and severity in the daily report

Examples

ActiveFile typeFile nameGrowth size thresholdGrowth size unitRemaining space after grow thresholdRemaining after UnitSeverity not enough space to growSeverity autogrow enabledAuto clearAlarm tagAlarmMetricReport
trueDataDBDATAG2W:70 W2M:85PercentG2W:30 W2M:10Percentcriticalwarningtruetruetruetruetrue

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

metricIdmetricUnitmetricTargetmetricDescription
MSSQL_DATABASE_GROWTH_SIZEMB[File Type] - [File name]The fetched growth size value for a given database file, expressed in MB
MSSQL_DATABASE_GROWTH_SIZEpercentage[File Type] - [File name]The fetched growth size value for a given database file, expressed in percentage
MSSQL_DATABASE_REMAINING_SPACE_AFTER_GROWMB[File Type] - [File name]Calculated remaining space in file after grow, expressed in MB
MSSQL_DATABASE_REMAINING_SPACE_AFTER_GROWpercentage[File Type] - [File name]Calculated remaining space in file after grow, expressed in percentage
/home/clients/8c48b436badcd3a0bdaaba8c59a54bf1/wiki-web/data/pages/products/promonitor/6.8/monitorsguide/mssql/ms-sqlautogrowth.txt · Last modified: 2024/05/01 18:35 (external edit)