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.0
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 and evaluate the size of the transaction logs in comparison to the database size within SQL Server instances. It provides alerts if the transaction log size exceeds specified warning or alarm thresholds.
Why: Monitoring the size of transaction logs is crucial because it helps in predicting the potential for database growth issues and ensures that the database does not run out of space. It also assists in maintaining the performance and stability of the SQL Server environments.
Manual checking: You can check the sizes manually in the database by issuing the following SQL commands:

SELECT name, size, max_size FROM sys.master_files WHERE type_desc = 'LOG'

Implementation Details

The job involves:

Checking transaction log sizes against the main database file sizes.
Issuing warnings if the transaction log size surpasses the percentage defined by the ‘warning threshold’.
Raising alarms if the size exceeds the percentage defined by the ‘alarm threshold’.
Providing options to ignore offline databases or specific databases in checks.

Database Criteria

The job targets:

Instances that are SQL Server 2000 editions with engines available.
It excludes databases as per specified by ignore lists and focuses on those not offline if specified.

Code Snippets

This job utilizes the following SQL procedures and tables:

CREATE TABLE dbw_trans_log_check_table (…)

CREATE PROCEDURE dbw_trans_log_check @taskID INT AS BEGIN … END

Dependencies

Dependencies include:

System tables like master.dbo.sysaltfiles and master.dbo.sysdatabases.
A custom procedure for checking log sizes named ‘sp_sqlperf_log’.

Alarm Configuration

The job uses parameters such as:

Min transaction log file size – Minimum size of the logs to be included in the check.
Warning and alarm thresholds – Percentages that trigger warnings and alarms when exceeded by the transaction log size relative to the database size.

Reporting

The job provides a report which includes:

Details of each database and transaction log with their sizes and status.
Options to view data on database file ratios and file locations.

Data Column	Description
Database	Names of the databases checked
Data size	Size of the data files
Data file count	Number of data files
Log size	Size of the log files
Log file count	Number of log files
Ratio	Ratio of log size to data size in percentage
Status	Current status based on thresholds
DB Status	Status of the database like online or offline
Log space used	Percentage of log space used

This output format ensures that administrators have a clear and organized view of the transaction log sizes relative to their databases, making it straightforward to identify potential issues.