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: | 2.1 | |
| Default schedule: | 10,40 * * * | |
| Requires engine install: | Yes | |
| Compatibility tag: | .[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2005′ & maj_version < ‘2017′ & hasengine=‘YES’ & (engine_edition = ‘Microsoft SQL Server’ | engine_edition = ‘Azure SQL Managed Instance’)] |
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 | The maximum number of databases to be collected in the history tables (sort by transaction log size). |
| include unallocated free space | YES | If 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). |
Job Summary
- Purpose: The purpose of this job is to monitor and manage the space usage of transaction logs in SQL Server databases, specifically for instances running versions between 2005 and 2017. This includes both Microsoft SQL Server and Azure SQL Managed Instance platforms.
- Why: This monitoring is crucial as it helps in maintaining the health and performance of the database by preventing transaction logs from becoming a bottleneck. It triggers warnings or alarms when the space usage thresholds are exceeded, indicative of potential issues like the need for transaction log backup or truncation to reclaim space.
- Manual checking: To check this manually, the following SQL command can be used:
DBCC SQLPERF(LOGSPACE)Job Description and Logic
The job leverages a stored procedure (`dbw_trans_log_usage`) to perform checks on transaction log space utilization:
- Determines whether a transaction log’s current usage and unallocated potential max size exceed pre-defined thresholds.
- Provides feedback if logs are within expected ranges or if intervention is necessary via warnings or alarms.
- Retains historical usage data for trend analysis and future reference.
The primary database script elements involved in this job include:
- Retrieval and filtration of data about transaction log sizes and percent usage using the `DBCC SQLPERF` command.
- Periodic data insertion into historical, current, and aggregate usage tables.
- Usage of cursor-based iteration over databases to fetch and process log usage data and execute conditional alerts based on the usage thresholds.
Job Configuration and Execution Details
- Scheduling: The default schedule for this job runs at 10 and 40 minutes past every hour, ensuring that the transaction logs are monitored closely throughout the day.
- Parameters Configuration: Several configuration parameters are set for tailoring the job’s operation such as warning, alarm, and historical data retention thresholds. Parameters also include settings to specify minimum log sizes to monitor and the maximum number of databases to include within the scope of this monitoring job.
- Dependencies: The job depends on several components internal to its operation, ensuring coherent and successive data flow. This includes stored procedures and helper tables handling current, historical, and aggregated log usage data.
Output and Reporting
The job provides detailed reporting that includes:
- Current and historical transaction log usage.
- Warnings and alarms when thresholds are exceeded.
- Visualization of data trends over time, helping database administrators to optimize database storage and performance.
Lastly, this job is crucial for maintaining operational stability and performance in production environments, ensuring robust database management practices concerning transaction log usage.