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

Purpose: The purpose of this job is to monitor and manage database disk capacity by checking free space on drives, where data and log files are located, to ensure that the system operates effectively without running out of disk space.
Why:
- This job is critical for preventing potential outages or performance degradation caused by insufficient disk space.
- Raises alarms or warnings based on predefined thresholds of disk space availability (either in percentage or MB), ensuring proactive handling of space issues.
Manual checking: You can check this manually in the database by querying system views or using tools that provide disk usage statistics.

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

This monitoring task is designed to trigger an alert if the free disk space falls below certain limits. These limits can be defined in either absolute terms (MB) or as a percentage of total space, adding flexibility based on different operational requirements.
The task can also evaluate disk space on volumes that do not contain database files if certain configurations are enabled (xp_cmdshell).

Dependency Information

The task depends on a set of stored procedures and tables for implementation and storing results.

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

Alerts are configurable for different thresholds to provide early warnings (Warning threshold) or critical alerts (Alarm threshold). This can be set as:
Warning Threshold (MB or %): Trigger a warning for low disk space.
Alarm Threshold (MB or %): Trigger an alarm for critically low disk space.
Additional parameters allow for the setup of these thresholds dynamically, specifying volumes and corresponding limits.
Integration with xp_cmdshell for expanded disk check capabilities across various file-system types.

Operational Reports and Statistics Collection

Collects statistics periodically as specified (Default every day).
Generates reports presenting current disk usage in absolute and percentage terms.
Offers historical views of disk space utilization over time, facilitating trend analysis and capacity planning.

Example Report Output

The system generates tables showing:
- Current disk space usage.
- Trends of disk space usage over time.
- Each disk’s status, including if it supports compression and if it is compressed, hence providing a comprehensive view of the disk infrastructure relevant to the database system.

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.