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

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:

Dependency and Script Implementations

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

SQL implementation details include:

Report Presentations

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

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