Transaction log space check

Job details

Name:	Transaction log space check
Platform:	Sybase
Category:	Capacity
Description:	Checks the amount of free space in all transaction logs.
Long description:	Checks the amount of free space in all transaction logs.
Version:	1.41
Default schedule:	5,15,25,35,45,55 * * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘sybase’]/instance[maj_version > ‘11′ & hasengine=‘YES’]

Name	Default value	Description
ignore databases		Names of databases which will be ignored from being checked (comma separated list).
ignore transaction logs mixed with data	YES	If YES, ignores databases which have mixed log and data.
warning threshold MB	2000	The check will give a warning when both, “warning threshold MB” and “warning threshold pct.”, falls below the threshold. The “warning threshold MB” is in MB, while the “warning threshold pct.” indicates the percentage threshold.
warning threshold pct.	10	The check will give a warning when both, “warning threshold MB” and “warning threshold pct.”, falls below the threshold. The “warning threshold MB” is in MB, while the “warning threshold pct.” indicates the percentage threshold.
alarm threshold MB	500	The check will give a alarm when both, “alarm threshold MB” and “alarm threshold pct.”, falls below the threshold. The “alarm threshold MB” is in MB, while the “alarm threshold pct.” indicates the percentage threshold.
alarm threshold pct.	5	The check will give a alarm when both, “alarm threshold MB” and “alarm threshold pct.”, falls below the threshold. The “alarm threshold MB” is in MB, while the “alarm threshold pct.” indicates the percentage threshold.
history threshold	72	All data older than the “history threshold” (in hours) will be deleted from the history table (dbw_log_space_usage_histr).

Purpose: This monitoring job is designed to check the amount of free space available in all transaction logs within a database system. It ensures that each transaction log has enough space to operate efficiently and avoid potential failures due to space constraints.
Why: Monitoring transaction log space is critical for maintaining database health and performance. Insufficient log space can lead to database errors or shutdowns, which can affect applications relying on the database. Timely alerts allow database administrators to manage space proactively.
Manual checking: To manually check transaction log space in the database, the following SQL commands can be used:

EXEC dbw_trlog_space_check

Scheduled Frequency: This job runs automatically at 5-minute intervals (5, 15, 25, 35, 45, 55 minutes past each hour), ensuring continuous monitoring without manual intervention.
Version: The script corresponds to version 1.41.
Acknowledgments Required: Yes. Acknowledgments are needed, emphasizing the critical nature of the alerts generated by this task.

Ignore Databases: Allows excluding specific databases from the check.
Ignore Transaction Logs Mixed with Data: If set to ‘YES’, logs that are mixed with data files are not included in the monitoring.
Warning Thresholds: Configured in MB and percentage. These thresholds set the limits at which a warning is triggered, indicating low space in transaction logs.
Alarm Thresholds: Similar to warning thresholds but set for more critical low-space conditions, prompting immediate attention.
History Threshold: Controls how long historical data on log space usage is retained, helping in trend analysis and post-issue forensics.

Dependencies: This job depends on certain procedures and tables like dbw_trlog_space_check, dbw_log_space_usage, and dbw_log_space_usage_histr to function correctly.
Cleanup on Fail: Specifies the cleanup actions to be taken if the job fails. This includes dropping specific procedures and tables that might have been created temporarily during the job execution.

The actual SQL code provided involves creating necessary tables and procedures to track and store transaction log usage details. Cursor management is used to handle multiple logs efficiently.
This script includes decision structures to handle various scenarios such as ignoring certain logs or checking the mix of log and data usage.
Parameters from the job configuration are dynamically applied to modify the script behavior during runtime.

Report Generator: The job includes a built-in report template that helps visualize transaction log usage across the database system.
It features a category-chart presentation of the largest transaction logs, highlighting their total size, used space, and free space.
Separate presentations for shared and individual transaction logs provide detailed insights, helping administrators understand the distribution of log usage.