Transactions log flushed bytes load
Job details
| Name: | Transactions log flushed bytes load | |
| Platform: | Sqlserver | |
| Category: | Performance | |
| Description: | Shows bytes flushed to transaction logs over time, total and top 5 databases. | |
| Long description: | Shows bytes flushed to transaction logs over time, total and top 5 databases. Shows bytes flushed to transaction logs over time, total and top 5 databases. |
|
| Version: | 1.3 | |
| Default schedule: | 2,12,22,32,42,52 * * * | |
| Requires engine install: | Yes | |
| Compatibility tag: | .[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version = ‘2000′ & hasengine=‘YES’ & (engine_edition = ‘Microsoft SQL Server’ | engine_edition = ‘Azure SQL Managed Instance’)] |
Parameters
| Name | Default value | Description |
|---|---|---|
| keep data for | 5 | Number of days to provide statistics for. |
Job Summary
- Purpose: The purpose of this job is to monitor and record the amount of data flushed to transaction logs over time for SQL Server instances, highlighting the total and top 5 databases.
- Why: This job is important as it helps identify patterns or spikes in transaction log activity which can be indicative of underlying database performance issues or potential optimizations. It can help in proactive monitoring and capacity planning to minimize the risk of database slowdowns or crashes due to transaction log overutilization.
- Manual checking: You can check this manually in the database by issuing these SQL commands:
SELECT cntr_name AS [Database Name], cntr_value/1024 AS [KB Flushed], instance_name AS [Database Instance], getdate() AS [Date] FROM [master].[dbo].[sysperfinfo] WHERE cntr_value > 0 AND counter_name LIKE 'Log Bytes Flushed/sec%' ORDER BY cntr_value DESC;Key Functionality and Execution
Dependencies
- System table: “master.dbo.sysprocesses”
- Historic and aggregate historic tables used for storing log flush details: “dbw_tr_log_flushed_bytes_load_histr”, “dbw_tr_log_flushed_bytes_load_aggr_histr”
- Help table: “dbw_tr_log_flushed_bytes_load”
- Self-dependency on the procedure: “dbw_tr_log_flushed_bytes_load_proc”
Implementation Details
The job creates and handles several tables and procedures:
- Tables for storing current values and historical differences.
- The main procedure “dbw_tr_log_flushed_bytes_load_proc” manages data collection, processing, and historical storage. This procedure populates historical tables based on differential calculations of flushed log bytes between snapshots.
- Periodic updates ensure the most recent six intervals are processed and the oldest data beyond the retention policy (parameter configured) is purged.
Report Generation
The job supports generating comprehensive reports that include:
- Transaction log activities per database.
- Time-based performance visualizations of log flush statistics.
- Average bytes flushed per minute, aggregated historically for better trending and analysis.
| Report Detail | Description |
|---|---|
| Transaction log – KB flushed history | Shows per-minute flush rates for top 5 databases and total, ordered by date. |
| Average KB flushed history | Displays average KB flushed per minute per day, helpful for identifying trends over time. |
Periodic Execution
- Default schedule for the job is every 10 minutes, making sure that data is frequently updated and kept relevant for real-time monitoring needs.
Performance considerations and tuning
- This job includes both real-time data collection and historical aggregation, ensuring that performance metrics are not just snapshots but also include trend analysis over configurable periods.
- Adjustments in the default collection interval and data retention settings can be made based on specific database usage patterns or performance issues identified during monitoring.
By continuously tracking and analyzing transaction log flushes, the dbWatch Control Center can help database administrators ensure their SQL Server instances run efficiently and are capable of handling the required load without unexpected issues related to transaction log capacities.