Transaction log space usage
Job details
| Name: | Transaction log space usage |
| Platform: | Sqlserver |
| Category: | Capacity |
| Description: | This procedure checks information returned by DBCC SQLPERF which is used to monitor the amount of space used in the transaction log. |
| Long description: | The procedure indicates (by warning or alarm) when to back up or truncate the transaction logs. If parameter ‘include unallocated free space’ is set to ‘YES’, the procedure will compute the free space in each transaction log file based on available space in each file plus all unallocated space this file (or files) could autoextend to (maxsize – current size). |
| Version: | 1.8 |
| Default schedule: | 10,40 * * * |
| Requires engine install: | Yes |
| Compatibility tag: | .[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version < ‘2008′ & hasengine=‘YES’ & engine_edition = ‘Microsoft SQL Server’] |
Parameters
| Name | Default value | Description |
|---|---|---|
| min histr interval | 55 | The minimum number of minutes before a new history record will be inserted in the history table. |
| warning threshold | 80 | The maximum percentage of space usage allowed in the transaction log before a warning is triggered. |
| alarm threshold | 95 | The maximum percentage of space usage allowed in the transaction log before an alarm is triggered. |
| history threshold | 96 | The number of hours the statistics are kept in the history table. |
| min transaction log size | 1000 | The minimum size (in MB) of the transaction log files to be examined. |
| max num db | 15 | |
| include unallocated free space | YES |
Job Summary
The purpose of this monitoring job configured in dbWatch Control Center is to manage and track the usage of transaction log space in SQL Server databases. The job leverages a suite of procedures and scheduled tasks to monitor space usage, perform necessary actions like inserting records into history, and issue warnings or alerts based on defined thresholds.
Detailed Overview
- Purpose: Monitoring transaction log space across SQL Server instances, particularly focusing on:
- Alert generation when the used space crosses the defined thresholds (warning and alarm).
- Maintaining a history of log usage to provide insights over time.
- Automatically managing log space to prevent potential database issues related to log file size constraints.
- Importance:
- Ensures that the transaction logs do not use space beyond what is specified, avoiding potential system slowdowns or crashes.
- Helps in planning and managing backups or truncations of the transaction logs based on the space usage trends captured over time.
- Manual Checking:
For manual verification and monitoring of transaction log space, one can use the SQL Server command:
DBCC SQLPERF(logspace)Implementations and Operations
- This job utilizes several SQL Server procedures and temporary tables such as:
- dbw_trans_log_usage: Main procedure for capturing current log usage.
- dbw_tans_log_usage_histr: Maintains historical data about log usage.
- dbw_tans_log_curent_usage: Stores current log usage details.
- dbw_tans_log_usage_aggr: Aggregates historical log usage data.
- Clearing and Aggregation: Older records are cleared from tables based on the parameters and conditions specified, like historical interval.
Dependencies
This job has several dependencies:
- Self-dependency: The job depends on the dbw_trans_log_usage procedure for execution.
- History and aggregation tables: Uses multiple auxiliary tables (dbw_tans_log_usage_histr, dbw_tans_log_curent_usage, dbw_tans_log_usage_aggr) for managing data.
- Supporting Procedures: The SQL Server system stored procedure sp_SQLPERF is used for fetching the LOGSPACE information.
Reporting and Alerts
- The job setup includes a complex reporting mechanism through the dbwatch-report-template which makes extensive use of SQL queries to fetch and display log usage data.
- Alerts and warnings are systematically generated and can be configured for dispatch through interfaces the system exposes, based on the execution status and outcomes determined by the log usage checks and threshold comparisons.
Update Mechanism
- The job includes scripted procedures for updating and maintaining the necessary SQL objects and structures on version updates, ensuring the monitoring remains consistent and accurate across database version changes.
Conclusion
This dbWatch monitoring job is a critical component for administrators managing SQL Server environments, specifically tuning around the transaction log usage, which is pivotal for maintaining system stability and performance. Regular updates and checks implemented through this setup assure that the system is kept within operational thresholds, aiding in effective database management.