Transaction log space usage
Job details
| Name: | Transaction log space usage | |
| Platform: | Sqlserver | |
| Category: | Capacity | |
| Description: | This procedure collects space usage in all filegroups defined in each database. The undocumented ‘sp_MSforeachdb’ Stored Procedure is used to execute T-SQL statements against dbo.sysfiles table in every database defined to a SQL Server instance. | |
| Long description: | This job checks information returned by DBCC SQLPERF which is used to monitor the amount of space used in the transaction log. | |
| Version: | 2 | |
| Default schedule: | 13 * * * | |
| Requires engine install: | No | |
| Compatibility tag: | .[type=‘instance’ & databasetype=‘sqlserver’]/instance[(engine_edition = ‘Microsoft SQL Server’ | engine_edition = ‘Azure SQL Managed Instance’)]→i/$i[maj_version > ‘2000′ & hasengine =‘NO’ & priv_processadmin = 0] |
Parameters
| Name | Default value | Description |
|---|---|---|
| ignore_databases | 360 | The list of databases to be ignored (separated by comma). |
| warning_threshold | 360 | The maximum percentage of space usage allowed in the transaction log before a warning is triggered. |
| alarm_threshold | 360 | The maximum percentage of space usage allowed in the transaction log before an alarm is triggered. |
| min_transaction_log_size | 360 | The minimum size (in MB) of the transaction log files to be examined. |
Job Summary
- Purpose: The purpose of this job is to monitor and manage the space used in the transaction logs within databases on Microsoft SQL Server or Azure SQL Managed Instance platforms.
- Why: This job is important because it helps prevent issues related to transaction log overflow that can disrupt database operations and performance. Monitoring transaction log space usage ensures that actions can be taken before thresholds are reached, thereby preventing potential service disruption or data loss.
- Manual checking: You can check this manually in the database by issuing the following SQL command:
DBCC SQLPERF(logspace)Detailed Description
This monitoring job, identified as “Transaction log space usage,” is used for tracking the amount of space consumed within transaction logs. Excessive log usage can indicate numerous insert, update, or delete operations and might signal a requirement for capacity review or log management strategies.
By default, the job examines all databases except those explicitly ignored (e.g., tempdb). It triggers warnings and alarms at specified thresholds, ensuring administrators are alerted to potential issues. Specific thresholds and minimum transaction log sizes can be set to tailor the job to the needs of the specific environment.
Implementation Details
- Category: Capacity
- Compatibility: The job is compatible with instances having no defined engine and types that are either Microsoft SQL Server or Azure SQL Managed Instance.
- Default Schedule: Job runs at 13 minutes past each hour.
- Timeout: The job will timeout if it takes longer than 120 seconds to complete.
Thresholds and Notifications
- The job uses two main thresholds to alert about transaction log space usage:
- Warning Threshold: When the log space used exceeds this percentage but is less than the alarm threshold.
Output and Reporting
Output from this job can be categorized into several sections, each offering insights on different aspects of transaction log usage:
Tables
| Category | Details |
|---|---|
| Transaction logs with warnings and/or alarms | Lists databases where the transaction log usage has surpassed the warning or alarm thresholds set. |
| Transaction logs checked | Shows transaction log usage information for all logs larger than the minimum size specified, even if they’re below the threshold. |
| Transaction logs ignored | Displays logs that were either too small to be significant or were explicitly configured to be ignored. |
Each presentation provides essential data for database administrators to assess and act upon to ensure optimal performance and capacity management within their SQL environments.