Transaction log space usage

Job details

Name:	Transaction log space usage
Platform:	Sqlserver
Category:	Capacity
Description:	This procedure checks information in sys.dm_db_log_space_usage view which shows space usage 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:	2.3
Default schedule:	10,40 * * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2016′ & hasengine=‘YES’ & (engine_edition = ‘Microsoft SQL Server’	engine_edition = ‘Azure SQL Managed Instance’)]

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	The maximum number of databases to be collected in the history tables (sort by transaction log size).
include unallocated free space	YES	If 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).

Job Summary

Purpose: This monitoring job is designed to assess the space usage in the transaction logs of SQL Server databases. It helps in identifying when the transaction logs are approaching capacity and suggests when to back up or truncate these logs to prevent potential database issues related to space constraints.
Why: Monitoring transaction log space usage is crucial because overflowing transaction logs can lead to database errors and interruptions in service. Early warnings allow for timely interventions like log backups or truncation, which maintains the health and performance of the database system.
Manual checking: To manually check the transaction log space usage, you can query the SQL Server view “sys.dm_db_log_space_usage” or similar system views that provide information about transaction log sizes and their usage statistics.

SELECT name AS 'Database', total_log_size_in_bytes/(1024*1024.) AS 'Total Log Size (MB)', used_log_space_in_percent AS 'Used Space (%)' FROM sys.dm_db_log_space_usage;

Configuration Details

As part of the job’s configuration, the following key aspects are set:

The job operates within the “Capacity” category indicating its role in space management.
It runs on a default schedule occurring at 10 and 40 minutes past every hour, demonstrating its function as a frequent monitoring tool.
Provides alarms and warnings based on thresholds for log usage to prompt necessary actions, ensuring logs do not reach critical levels.

Dependency and Script Implementations

Dependencies are set up on various internal tables and procedures which support the job:

“dbw_trans_log_usage” – Main procedure that performs log space checks.
“dbw_tans_log_usage_histr” – Table used for maintaining historical data of log usage.
“dbw_tans_log_curent_usage” – Table used for tracking current log usage statistics.
“dbw_tans_log_usage_aggr” – Aggregation table likely used for compiling and summarizing log data across different intervals.

SQL implementation details include:

Creation of tables to store current, historical, and aggregated log usage data.
Procedures are established to capture, evaluate, and act upon the configured thresholds for warnings and alarms.

Report Presentations

In the Report Template, several presentations are designed to provide insights:

“Current transaction log space usage” – Displays real-time data of log size and percentage used.
“Transaction log space usage history” – Shows trends over time to aid in understanding usage patterns.
Tables and charts are built into the presentations to visualize data effectively, allowing for easier interpretation and decision-making.

This job is essential in maintaining optimum database performance and preventing issues related to transaction log overflow, ensuring system stability and reliability.