Disk space check
Job details
| Name: | Disk space check |
| Platform: | Sqlserver |
| Category: | Capacity |
| Description: | Checks the amount of free space on the available disk drives. |
| Long description: | Checks the amount of free space on the available disk drives using an undocumented SQL Server extended stored procedure xp_fixeddrives. |
| Version: | 1.2 |
| Default schedule: | 10 5,17 * * |
| Requires engine install: | Yes |
| Compatibility tag: | .[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘1999′ & hasengine=‘YES’ & engine_edition = ‘Microsoft SQL Server’ & eng_inst_priv = 0] |
Parameters
| Name | Default value | Description |
|---|---|---|
| ignore drives | These drives will be ignored. (Comma separated list) | |
| warning threshold | 6000 | The check will give a warning if free disk space falls below this value (in MB). |
| alarm threshold | 1000 | The check will give an alarm if free disk space falls below this value (in MB). |
Job Summary
- Purpose: This monitoring job serves to check the free space available on disk drives within SQL Server instances, ensuring system performance and data storage capacity are not compromised.
- Why: Monitoring disk space is critical for preventing server crashes or sluggish performance that can arise from full disk drives. This job alerts administrators before reaching critical points, allowing for proactive management.
- Manual checking: To manually check disk space on SQL Server, use the following command:
EXEC xp_fixeddrivesJob Execution Details
- Task Category: Capacity
- Scheduled Execution: It runs twice a day at 05:10 and 17:10.
Description of Procedure:
This DBWatch job calls a defined SQL Server stored procedure “dbw_disk_space_check” that uses the undocumented stored procedure “xp_fixeddrives” to fetch disk drive data. This process involves:
- Checking against specified drives to ignore.
- Inserting current disk space data into a table for monitoring.
- Evaluating space for setting warning or alarm thresholds based on predefined parameters.
Logic Applied:
- Deletes previous data in a temporary table.
- Fetches current disk space usage data.
- Filters out the ignored drives.
- Inserts new data into a historical record table.
- Determines the least space available on drives and checks against set warning and alarm thresholds.
- Returns a notification level depending on the severity.
Dependencies and Tables
Dependencies:
- dbw_disk_space_check
- dbw_disk_space_histr
- dbw_disk_space_last
Tables Used:
- “dbw_disk_space_histr” which records historical disk space data.
- “dbw_disk_space_last” which holds the most recent disk space data.
Dependency Management:
- On failure, dependent objects are cleaned up to ensure data integrity and consistency across monitoring cycles.
Reporting Framework
Version: 2.0
Report Title: Disk Space Check
Frequency: Hourly updates
Report Outline:
- Presents the latest free disk space available per drive.
- Tracks historical changes of disk space availability.
Detailed Table Definitions:
| Column Name | Data Type | Description |
|---|---|---|
| Drive | String | Identifier of the disk drive |
| Free | Decimal | Available space in GB |
| Date | Date | The date of the record |
Charts Configuration:
- Line charts depicting the trend of free space across different time points, categorizing per drive.
Presentation Format:
- Tabular data alongside graphical representations for easy analysis. Includes both current values and historical trends.
This job configuration provides a comprehensive oversight over disk capacity, central to maintaining optimal database performance in SQL Server environments.