Transaction log space usage


Job details

Name: Transaction log space usage
Platform: Sqlserver
Category: Capacity
Description: This procedure checks information returned by DBCC SQLPERF which is used to monitor the amount of space used in the transaction log.
Long description: The procedure indicates (by warning or alarm) when to back up or truncate the transaction logs. If parameter ‘include unallocated free space’ is set to ‘YES’, the procedure will compute the free space in each transaction log file based on available space in each file plus all unallocated space this file (or files) could autoextend to (maxsize – current size).
Version: 1.8
Default schedule: 10,40 * * *
Requires engine install: Yes
Compatibility tag: .[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version < ‘2008′ & hasengine=‘YES’ & engine_edition = ‘Microsoft SQL Server’]

Parameters

Name Default value Description
min histr interval 55 The minimum number of minutes before a new history record will be inserted in the history table.
warning threshold 80 The maximum percentage of space usage allowed in the transaction log before a warning is triggered.
alarm threshold 95 The maximum percentage of space usage allowed in the transaction log before an alarm is triggered.
history threshold 96 The number of hours the statistics are kept in the history table.
min transaction log size 1000 The minimum size (in MB) of the transaction log files to be examined.
max num db 15
include unallocated free space YES

Job Summary

The purpose of this monitoring job configured in dbWatch Control Center is to manage and track the usage of transaction log space in SQL Server databases. The job leverages a suite of procedures and scheduled tasks to monitor space usage, perform necessary actions like inserting records into history, and issue warnings or alerts based on defined thresholds.

Detailed Overview

DBCC SQLPERF(logspace)

Implementations and Operations

Dependencies

This job has several dependencies:

Reporting and Alerts

Update Mechanism

Conclusion

This dbWatch monitoring job is a critical component for administrators managing SQL Server environments, specifically tuning around the transaction log usage, which is pivotal for maintaining system stability and performance. Regular updates and checks implemented through this setup assure that the system is kept within operational thresholds, aiding in effective database management.