Transaction log size check


Job details

Name: Transaction log size check
Platform: Sqlserver
Category: Capacity
Description: This procedure checks the size of the transaction log (log file(s) size), and compares it to the database size (data file(s) size). If the size of the transaction log file(s) in percentage is greater than the warning/alarm parameter value, the check returns a warning/alarm.
Long description: This procedure checks the size of the transaction log (log file(s) size), and compares it to the database size (data file(s) size). If the size of the transaction log file(s) in percentage is greater than the warning/alarm parameter value, the check returns a warning/alarm.
Version: 2.2
Default schedule: 10 5,17 * *
Requires engine install: Yes
Compatibility tag: .[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2000′ & hasengine=‘YES’ & eng_inst_priv = 0 & (engine_edition = ‘Microsoft SQL Server’ engine_edition = ‘Azure SQL Managed Instance’)]

Parameters

Name Default value Description
min transaction log size 2500 The minimum size (in MB) of the transaction log files that will be examined.
warning threshold 100 The threshold (in percent) to trigger a warning. If the size of the transaction log file is greater than f.ex. 100% (the default value of the parameter) of the total size of the data file(s), the check returns a warning.
alarm threshold 200 The threshold (in percent) to trigger an alarm. If the size of the transaction log file is greater than f.ex. 200% (the default value of the parameter) of the total size of the data file(s), the check returns an alarm.
ignore offline databases NO If set to YES, the check will ignore databases which are offline.
ignore databases The list of databases to ignore (separated by comma).
change status when not full 95 If the transaction log file is not full (default value – 95% full) the alert will only trigger a warning.

Job Summary

SELECT database_name, log_size_mb, data_size_mb, (log_size_mb/data_size_mb)*100 AS log_to_data_ratio FROM sys.databases; -- Modify this query based on your actual database environment.

Details of the Monitoring Job

Table: Key Components Description

Component Description
Category Capacity
Name Transaction log size check
Description Checks the size ratio of transaction log to database size, alerting if specific thresholds are exceeded.
Company dbwatch.no
Default Schedule Twice daily at 05:00 and 17:00
Version 2.2

Table: Parameters and Their Descriptions

Parameter Default Value Description
Minimum transaction log size 2500 MB Specifies the minimum log size that will trigger the checking process.
Warning threshold 100% The log size ratio that will trigger a warning.
Alarm threshold 200% The log size ratio that will trigger an alarm.
Ignore offline databases NO Determines whether offline databases should be ignored by the check.
Change status when not full 95% A threshold to change the status if the log is not filled up to a certain percentage.

Table: Dependencies and Their Utility

Dependency Object Description
master.dbo.sysaltfiles Dependency table for tracking alternative file details in a database.
master.dbo.sysdatabases A reference table holding details about the databases within the instance.
dbw_trans_log_check A recursive dependency to the task itself for internal checks and balances.
sp_sqlperf_log A stored procedure that runs DBCC SQLPERF for analyzing log space usage.

Implementation and Reporting