Database disk capacity


Job details

Name: Database disk capacity
Platform: Sqlserver
Category: Capacity
Description: Checks free space on drives where data and transaction log files are defined. An alarm (or warning) is raised if the percentage limit is reached OR if the absolute limit is reached. IF the xp_cmdshell Instance configuration option is enabled the alert can check disk and mounted volumes where data-files are not present.
Long description: From version 1.7 an alarm (or warning) is raised if the percentage limit is reached OR the absolute limit is reached. Not both as in older versions of this task. IF the xp_cmdshell Instance configuration option is enabled the alert can check disk and mounted volumes where data-files are not present.
Version: 2.3
Default schedule: 15 5,17 * *
Requires engine install: Yes
Compatibility tag: .[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2008′ & hasengine=‘YES’ & engine_edition = ‘Microsoft SQL Server’]

Parameters

Name Default value Description
warning threshold (MB) Minimum of free space (in MB) per disk drive (format: volume=value, e.g. C:\=6000, D:\=7000). If the amount of free space falls below this parameter value the check will return a WARNING.
alarm threshold (MB) Minimum of free space (in MB) per disk drive (format: volume=value, e.g. C:\=6000, D:\=7000). If the amount of free space falls below this parameter value the check will return an ALARM.
warning threshold (percent) Warning threshold for the minimum percentage value of the free space per disk drive (format: volume=value, e.g. C:\=10, D:\=20). If the amount of free space falls below this parameter value the check will return a WARNING.
alarm threshold (percent) Alarm threshold for the minimum percentage value of the free space per disk drive (format: volume=value, e.g. C:\=5, D:\=5). If the amount of free space falls below this parameter value the check will return an ALARM.
collect statistics 1 This parameter controls how often (in days) statistics are collected in to history table.
enable xp_cmdshell NO If set to “YES“ the Alert will enable the xp_cmdshell Instance configuration option (if it is not enabled). This is required to run operation system “wmic” command to collect space usage statistics for local and mounted disk drives where database-files are not present.
disable xp_cmdshell NO If set to “YES“ (and if the xp_cmdschell option is enabled) the Alert will disable the xp_cmdschell option after execution of the Alert. As a security best practice it is recommended to only enable xp_cmdschell option for the duration of the actual Alert that requires it.
combine thresholds YES This parameter allows you to combine the conditions of both the “alarm threshold (percent)” and “alarm threshold (MB)” or “warning threshold (percent)” and “warning threshold (MB)” parameters. When set to “YES“, an alarm/warning will only be triggered if both the percentage and MB threshold values are exceeded simultaneously. This setting provides an additional level of control, ensuring that alarms/warnings are only activated when both thresholds are surpassed, minimizing false positives and unnecessary alerts.

Job Summary

SELECT volume_mount_point, file_system_type, size_mb, free_mb FROM sys.master_files AS f CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id)

Detailed Description

Dependency Information

Object Description
dbw_db_disk_capacity_proc Main procedure that implements the monitoring logic and condition checks.
dbw_disk_capacity_histr_tab History table storing historical disk capacity usage metrics.
dbw_disk_capacity_detailed_tab Detailed table that captures instant snapshot data about disk usage.
dbw_disk_capacity_wmic_output Table used to store intermediate output from disk status queries.
dbw_disk_capacity_wmic_detailed Detailed table used for formatting output from system command for disk status.

Alert Configuration

Operational Reports and Statistics Collection

Example Report Output

In summary, the dbWatch job for monitoring database disk capacity is a crucial task that supports the prevention of database service disruptions due to disk space shortages, while providing detailed reporting and alerting capabilities to manage the database storage infrastructure efficiently.