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 monitoring job titled “Transactions log flushed bytes load” focuses on tracking the amount of data written to the transaction logs in SQL Server and Azure SQL Managed Instances. It assesses these values over time and identifies the top 5 databases with the highest activity.
- Why: This job is significant as tracking transaction log flushes is crucial for understanding database write loads, identifying potential I/O bottlenecks, and ensuring that transaction logs are efficiently managed. High levels or sudden spikes in flushed bytes can indicate heavy transaction activity or potential issues needing closer examination.
- Manual checking: To check this manually in the database, the following SQL commands can be used:
bc. SELECT cntr_value AS ‘Bytes Flushed’, instance_name AS ‘Database Name’ FROM master.dbo.sysperfinfo WHERE counter_name LIKE ‘Log Bytes Flushed/sec%’ ORDER BY cntr_value DESC
Implementation Details and Background Operations
- The job relies on multiple SQL operations to manage and update historical records for transaction log activities:
- Tables such as “dbw_tr_log_flushed_bytes_load”, “dbw_tr_log_flushed_bytes_load_histr”, and “dbw_tr_log_flushed_bytes_load_aggr_histr” are created to store raw data, differential data, and aggregated statistical data respectively.
| Object |
Description |
Data Type |
| dbw_tr_log_flushed_bytes_load |
Stores recent transaction log flush data |
Table |
| dbw_tr_log_flushed_bytes_load_histr |
Historical log of differential flush data |
Table |
| dbw_tr_log_flushed_bytes_load_aggr_histr |
Aggregated historical data for analytical purposes |
Table |
| dbw_tr_log_flushed_bytes_load_proc |
Main procedure to manage data capturing and processing |
Procedure |
Job Execution and Monitoring
- Default Schedule: The job is configured to execute periodically at intervals of every 10 minutes, following a schedule defined as “2,12,22,32,42,52 * * *”.
- Error Handling: The procedure includes TRY…CATCH logic to handle exceptions and ensure robustness in its operation, logging any occurring errors for debug and maintenance purposes.
Reporting Capabilities
- Reports generated by this job provide insights into the transaction load, specifically indicating the rate of kilobytes flushed to the transaction log per minute.
- Detailed reports allow for monitoring both the total and per-database metrics:
- Table and historical view over time.
This systematic monitoring of transaction log flushes addresses the needs for performance tuning, capacity planning, and can alert DBAs to potential issues before they impact database operations.