Database space check

Job details

Name:	Database space check
Platform:	Sybase
Category:	Capacity
Description:	Checks the amount of free space in all databases.
Long description:
Version:	2.1
Default schedule:	10 5,17 * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘sybase’]/instance[maj_version = ‘12′ & hasengine=‘YES’]

Parameters

Name	Default value	Description
ignore databases		Names of databases which will be ignored (comma separated list).
min. free space (MB)	2000	No warning or alarm will be triggered if the amount of free space in a device fragment is higher then this value.
warning threshold pct.	90	The check will give a warning when the amount of free space in a device fragment is lower then the parameter value “min. free space (MB)”, and the percentage of used space exceeds the threshold value “warning threshold pct.”.
alarm threshold pct.	95	The check will give an alarm when the amount of free space in a device fragment is lower then the parameter value “min. free space (MB)”, and the percentage of used space exceeds the threshold value “alarm threshold pct.”.

Job Summary

Purpose: The purpose of this job is to monitor and report on the free and used space across all databases in a Sybase instance to ensure that space constraints do not impact database performance and operations.
Why: This job is critical for preventing potential outages or performance degradations that can occur when databases run out of space. Proactively managing space utilization ensures that databases can operate efficiently and without interruption.
Manual checking: You can check this manually in the database by issuing these SQL commands:

select name, size, free_space from sysdatabases

Scope of Monitoring

Applies to all databases in Sybase instances with version 12 and engine available (based on filters specified in the XML job configuration).
Monitors free and used space across database storage, segments, and log spaces.

Comprehensive Checks and Operations

Determines space usage for database files and logs, checking against predefined thresholds for generating warnings or alarms.
Dynamically ignores databases listed by the user to focus monitoring on relevant datasets.

Dependencies

Dependents largely on self-contained scripts for assessing space and includes references to several support tables and procedures that set up the necessary data environment for the checks.

Implementation Details

Involves creation of multiple tables to store space usage statistics.
A stored procedure (dbw_database_space_check) is implemented to aggregate and compute space statistics, check against predefined thresholds, and populate these tables.
Tables such as dbw_db_space_usage and dbw_db_segm_space_usage are used during the execution of the job to capture and log details about space utilizations.

Cleanup and Recovery

Includes steps to drop the procedure and associated tables if the job fails, ensuring a clean state before any rerun.

Reporting

Provides detailed tables and reports indicating space used by database names, segment types, and device fragments.
Charts and visual presentations are available to intuitively display space distribution across databases and devices.

Tables in Report

Database Name	Total Size (MB)	Used Space (MB)	Free Space (MB)	Transaction Log (MB)
Database 1	10240	5120	5120	2048
Database 2	20480	15360	5120	4096

Note: Values in the table are hypothetical and should be replaced with actual data from reporting outputs.

Scheduling

Default scheduled to run at 5:10 am and 5:10 pm every day, ensuring metrics are updated twice daily to capture current space utilization across the monitored database environment.

These thorough monitoring activities confirm the system’s health concerning space utilization, assisting database administrators in proactive space management and operational uptime.