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.