Disk space check
Job details
| Name: | Disk space check |
| Platform: | Sqlserver |
| Category: | Capacity |
| Description: | This procedure collects space usage in all filegroups defined in each database. The undocumented ‘sp_MSforeachdb’ Stored Procedure is used to execute T-SQL statements against dbo.sysfiles table in every database defined to a SQL Server instance. |
| Long description: | Checks the amount of free space on the available disk drives using an undocumented SQL Server extended stored procedure xp_fixeddrives. |
| Version: | 2 |
| Default schedule: | 20 * * * |
| Requires engine install: | No |
| Compatibility tag: | .[type=‘instance’ & databasetype=‘sqlserver’]/instance[engine_edition = ‘Microsoft SQL Server’]→i/$i[maj_version > ‘1999′ & hasengine =‘NO’ & eng_inst_priv = 0] |
Parameters
| Name | Default value | Description |
|---|---|---|
| ignore_drives | 360 | The list of drives to be ignored (separated by comma). |
| warning_threshold | 360 | The check will give a warning if free disk space falls below this value (in MB). |
| alarm_threshold | 360 | The check will give an alarm if free disk space falls below this value (in MB). |
Job Summary
- Purpose: Monitor disk space on SQL Server instances and provide alerts based on defined thresholds to ensure that the database does not run out of disk space unexpectedly.
- Why: Monitoring disk space is crucial to avoid system crashes, performance deterioration, or complete service failures due to insufficient disk space. Ensuring adequate free space is essential for the smooth operation of databases that could grow dynamically.
- Manual checking: You can check disk space manually on SQL Server using the extended stored procedure:
exec master.dbo.xp_fixeddrivesJob Description
The “Disk space check” job is primarily aimed at checking the free space available on disk drives for instances of SQL Server where the version is later than 1999 and the engine is marked as not having an engine instance private (eng_inst_priv = 0). This job is critical for capacity planning and operational stability.
Long Description
This job uses an undocumented SQL Server stored procedure “xp_fixeddrives” to fetch free disk space data from drives, excluding any user-specified drives. It categorizes the output into ‘ALARM’, ‘WARNING’, or ‘OK’ based on the thresholds set for disk space:
- ALARM: Free disk space is below 1000 MB
- WARNING: Free disk space is below 5000 MB but above or equal to 1000 MB
- OK: Free disk space is above the warning threshold
Ignore listing is also considered, where specified drives are excluded from checks and their status will be displayed as ‘IGNORED’.
Compatibility and Requirements
This monitoring job is compatible with SQL Server instances that do not have an engine and are identified as instance types requiring special monitoring due to their configuration or critical nature.
Additional Outputs
This job provides not only the status and alarm details but also a comprehensive list of disks with their current free space in an easy-to-understand format. It helps in visualizing which disks are nearing capacity and which ones are well within safe limits.
Job Details Table
The below table provides indications on the results of the disk space checks, categorizing them into different statuses based on the available space.
| Disk Name | Status | Free Space |
|---|---|---|
| Disk1 | OK | 2100 MB |
| Disk2 | WARNING | 4500 MB |
| Disk3 | ALARM | 900 MB |
| Disk4 | IGNORED | 3000 MB |
Scheduling and Automation
- Default Schedule: Job runs every 20 minutes.
- Execution Time Limit: 120 seconds per run.
This automated monitoring helps in maintaining the operational health of database systems by proactively managing disk space, thus avoiding any outages or disk space-related issues that might occur due to oversight or manual monitoring processes.