Database disk space usage
Job details
| Name: |
Database disk space usage |
| Platform: |
Sqlserver |
| Category: |
Capacity |
| Description: |
Checks free space on drives where all data and transaction log files are defined. Drives where no data files exist will be ignored. |
| Long description: |
Checks free space on drives where all data and transaction log files are defined. Drives where no data files exist will be ignored. |
| Version: |
1.6 |
| Default schedule: |
10 5,15 * * |
| 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 |
| warning threshold |
6000 |
The check will give a warning when disk space falls below this value (in MB). |
| alarm threshold |
1000 |
The check will give an alarm when disk space falls below this value (in MB). |
Job Summary
- Purpose: The monitoring job is designated for tracking the disk space usage specifically on SQL Server instances to maintain adequate free disk space and prevent potential service disruptions due to space shortage.
- Why: The job is significant as it helps in proactively managing and ensuring the availability of sufficient disk space on the drives holding SQL Server data and transaction log files. By setting warning and alarm thresholds, it allows database administrators to take timely action to prevent database crashes or performance degradation.
- Manual checking: To manually check disk space usage in the database, the following SQL command can be used:
select drive_letter, type, db_name, logic_name, file_name, size_mb, growth_kb * 8 as Growth, case max_size when '-1' then 'Unrestricted' else round(cast((max_size / 1024.0) as float), 2) end as MaxSize from dbw_file_usage_detailed order by size_mb desc
Task Details
- Category: Capacity
- Object Monitored: dbw_db_disk_usage_proc
Reporting Templates
- The task reports disk space check with detailed presentations showing:
- Drive, file type, database, logical and physical filename, file size, file growth, and maximum file size.
- Charts and graphs for visual representation of disk space distribution across various file types and databases.
Specific Output Presentations Include:
- Disk space usage details table sorted by size:
- Columns: Drive, Type, Database, Logical filename, Filename, Size (MB), Growth (KB), Max size (Unrestricted or specific limit).
- Disk usage charts:
- Categorize disk space into free space, data files, transaction log files, tempdb files, and system database files.
- Disk usage per database:
- Display top 10 space usage by database type, focusing more distinctively on how space is utilized among different databases.
Dependencies
- This job has dependencies on several internal object tables and calls to functions like `xp_fixeddrives` for getting real-time disk space data.
Implementation
- SQL Procedures:
- A stored procedure to monitor and record the disk space details, set alerts based on thresholds and manage disk space usage logs.
- Tables storing detailed and summary information about disk space usage are created and maintained to provide history tracking and quick reference.
Upgrade and Maintenance
- Upgradation from previous versions involves altering stored procedures to match new reporting and alerting requirements.
- Ensures data integrity and smooth transition with upgraded features like better summary, detailed reporting and enhanced alert mechanisms.
The proactive approach in monitoring and quick alerting allows for optimal database management, preventing potential system failures due to insufficient disk space.