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

MS-SQL Used space in file

Purpose

  • The disk space allocated to a data file is logically divided into pages and extents
  • Being able to monitor database files used space evolution becomes then important to stablish a good database storage strategy.

Monitor features include :

  • Monitoring of every SQL Server file type (Data - row -, Log …) and specific file name
  • Able to alert if used space in a file (of any type) has reached a given threshold expressed in MB or percenatage

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 rule of monitoring.
  • You can combine multiple rules to cover different cases.
  • Within a rule, you can configure the SQL Server file type you want to monitor, specific file name(s) using regular expressions, a used space in file threshold expressed in MB or percentage

File type
Select the specific SQL server file type you want to monitor , select “Any” to monitor used space in any kind of file, select “Aggregate if you need to monitor the total used space of all database files

File name:
You can set a file specific rule by setting its name in the File name filter field. Use * to match any file name, or use regular expressions.

Used space in file:
Use the multi-threshold syntax to set multiple threshold/severity associations: G2W:10 W2M:60 M2C:90 (Green To Warning, Warning To Major, etc…), expressed in MB or percentage An alarm of the corresponding severity will be sent if used sace in file is over threshold. Set 0 in the field if unused

Notice that values retrieved in MB will be rounded

Surveillance table

ParameterDescription
ActiveUse this field to activate or deactivate a line of configuration.
File typeSelect the specific SQL Server type of file you want to monitor Select “Any” so to monitor every type of file Select “Aggregate” if you want to monitor the used space of all database files (aggregated)
File nameA filter to match only a specific SQL Server file name, you can use regular expression, white or black list
Used space in fileThe threshold for the used space in file You can use the multi threshold syntax (i.e. : G2W:10 W2C:40) . Values can be expressed in MB or Percentage
UnitRepresents the used space in file unit of measure
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 nameUsed space in fileUnitAuto clearAlarm tagAlarmMetricReport
trueDataDBDATAG2W:60 W2M:85Percentagetrue truetruetrue

Effect : For this rule, only Data - rows - type files will be monitored (File Type set to : “Data”).

All Data type files containing the string “DBDATA” on their file names will be taken into account. A WARNING alarm is sent if used space in file is 60% or more, a MAJOR alarm if 85% or more.

Generated metrics

metricIdmetricUnitmetricTargetmetricDescription
MSSQL_DATABASE_AGG_USED_SPACE_IN_FILE_MBMBAGGREGATEThe addition of all retrieved SQL Server files used spaces - aggregated - of a given database, expressed in MB
MSSQL_DATABASE_AGG_USED_SPACE_IN_FILE_PERCENTPercentageAGGREGATEThe addition of all retrieved SQL Server files used spaces - aggregated - of a given database, expressed in percentage
MSSQL_DATABASE_USED_SPACE_IN_FILE_MBMB[File type] - [File name]Retrieved SQL Server file used space, expressed in MB
MSSQL_DATABASE_USED_SPACE_IN_FILE_PERCENTPercentage[File type] - [File name]Retrieved SQL Server file used space, expressed in percentage
/home/clients/8c48b436badcd3a0bdaaba8c59a54bf1/wiki-web/data/pages/products/promonitor/6.8/monitorsguide/mssql/ms-sqlusedspaceinfile.txt · Last modified: 2024/05/01 18:35 (external edit)