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

Purpose: The purpose of this job is to monitor the size of the transaction log in comparison to the database’s data file size and issue warnings or alarms based on predefined thresholds.
Why: Monitoring the transaction log size is critical as excessive growth can indicate issues such as uncommitted transactions or a need for more frequent backups. If the log size is disproportionate, it may lead to performance degradation or unavailability of the database.
Manual checking: To manually check the transaction log size against the data file size, the following SQL commands can be used in a SQL Server environment:

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

The procedure dbw_trans_log_check is implemented to fetch data from system tables, calculate data and log file sizes, assess them against set thresholds, and log results accordingly.
A report is generated that includes detailed listings of databases, their corresponding data and log file sizes, the usage ratio, and whether they’ve hit warning or alarm statuses.

This monitoring task is vital in proactive database management by ensuring the transaction log does not unexpectedly expand to a size that could affect database operations and performance. Monitoring and timely response to the logs’ growth help maintain overall database health and service continuity.