Data file size check
Job details
| Name: | Data file size check |
| Platform: | Sqlserver |
| Category: | Capacity |
| Description: | Checks the remaining space for all databases where the data files are set with limited growth rate (max size not unlimited). |
| Long description: | Checks the remaining space for all databases where the data files are set with limited growth rate (max size not unlimited). |
| Version: | 1.9 |
| Default schedule: | 5 7,12,17 * * |
| Requires engine install: | Yes |
| Compatibility tag: | .[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version = ‘2000′ & hasengine=‘YES’ & engine_edition = ‘Microsoft SQL Server’] |
Parameters
| Name | Default value | Description |
|---|---|---|
| ignore databases | model | List of databases (separated by comma) which will be ignored. |
| warning threshold | 90 | Percentage value when a warning is triggered. |
| alarm threshold | 95 | Percentage value when an alarm is triggered |
| minimum free space | 10000 | The minimum free space (in MB) left for data file(s) to grow. If the amount of free space falls below this parameter value, and at the same time the amount of free space in percent falls below the “warning/alarm threshold” parameter value, the check will return a WARNING/ALARM. |
Job Summary
- Purpose: The main purpose of this monitoring job is to ensure that all databases in a SQL Server instance (specifically version 2000 with SQL Server Engine) have adequate data file space for operations that require database growth.
- Why: This job is critical as it monitors and provides alerts on data file sizes to prevent any potential database disruptions due to insufficient space, which is crucial in maintaining database health and availability. Triggering warnings and alarms based on specified thresholds helps in proactive space management.
- Manual Checking: To manually check data file sizes and their growth potential, database administrators can run SQL queries to extract file size information from the system tables or use database administration tools provided by SQL Server.
Implementation Details
- The procedure ‘dbw_data_file_size_proc’ is created to handle the monitoring. It filters databases to be ignored, checks file sizes, and inserts results into ‘dbw_data_file_size_tab’.
- It executes under various conditions, such as ignoring databases listed by the user, and checks each database’s file size against maximum thresholds to determine if a warning or alarm state should be triggered.
- Acknowledgments are handled within the procedure to reflect the status update in dbWatch.
Job Output
- The monitoring script outputs the percentages of space used and sends alerts if these percentages exceed the warning or alarm thresholds set by the administrator. The following table layout is typically expected from this job:
| Database | Group ID | File Count | Allocated Space | Lowest Value of Max Size | Total Max Size | Pct Allocated |
|---|---|---|---|---|---|---|
| Database Name | Group ID | Number of Files in Group | Space Used (MB) | Minimum Configured Max Size (if applicable) | Configured Max Size | % of Max Size Used |
- Additionally, for each critical alert or warning generated, detailed logs are provided which include the database name, group ID, the percentage of space used, and the corresponding threshold event (warning or alarm).
Job Dependencies and Schedule
- This job has a dependency on its own tables and procedures, specifically ‘dbw_data_file_size_proc’ for processing the data and ‘dbw_data_file_size_tab’ for storing the results.
- Scheduling is typically configured to run at specific intervals during the day (e.g., at 7 AM, 12 PM, and 5 PM).
Enhancements and Maintenance
- Over time, upgrade scripts are utilized to enhance or adjust the procedure as per new requirements or optimizations.
- Administrators can update the job to include new SQL Server versions or modify thresholds and ignored databases as necessary.
This job is a crucial part of the database monitoring landscape, especially for systems where data growth is highly dynamic and needs constant supervision.