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

exec master.dbo.xp_fixeddrives

Job 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:

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

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.