During a recent customer engagement, we investigated sustained transaction log growth in Azure SQL Database without obvious large user transactions. The customer was familiar with PostgreSQL diagnostics and wanted to understand how similar insights can be obtained in Azure SQL Database—especially around Class 2 (system) transactions.
This post summarizes what we discussed, explains why Azure SQL behaves differently, and walks through practical DMV‑based monitoring patterns you can use today.
Azure SQL Database vs. PostgreSQL: Diagnostic Model Differences
One of the first clarifications we made is that Azure SQL Database does not expose diagnostic settings equivalent to PostgreSQL’s system‑level log diagnostics.
Azure SQL Database is a fully managed PaaS service, and many internal operations—such as checkpoints, version store cleanup, and background maintenance—are abstracted from direct control. Instead of low‑level engine logs, Azure SQL provides cumulative Dynamic Management Views (DMVs) that expose the effects of system activity rather than the internal implementation.
What Are Class 2 Transactions?
In Azure SQL Database, Class 2 transactions generally refer to system‑generated transactions, not directly initiated by user workloads. These commonly include:
- Checkpoint operations
- Version store cleanup
- Ghost record cleanup
- Background metadata maintenance
Although they are not user‑driven, these transactions still generate transaction log activity, which can be surprising when log usage grows steadily without large user transactions.
Key DMVs to Monitor Class 2 Activity
1. Transaction Log Usage
SELECT *
FROM sys.dm_db_log_space_usage;This DMV provides:
- Total log size
- Used log space
- Used log percentage
If log usage grows steadily without large user transactions, it is often a signal that background system activity (Class 2 transactions) is responsible.
- Checkpoint Activity
SELECT *
FROM sys.dm_exec_requests
WHERE command = 'CHECKPOINT';Frequent checkpoints result in:
- More frequent log flushes
- Increased system log writes
In Azure SQL Database, checkpoint frequency is system‑managed and cannot be tuned through configuration or diagnostic settings.
- Version Store Usage (Common Class 2 Contributor)
SELECT *
FROM sys.dm_tran_version_store_space_usage;High version store usage often leads to:
- Background cleanup tasks
- Increased system transactions
- Additional transaction log generation
This is especially common in workloads using:
- Snapshot Isolation
- Read Committed Snapshot Isolation (RCSI)
- Long‑running transactions or readers
Automating Monitoring with Azure Elastic Jobs
Because these DMVs are cumulative, capturing them over time is key. During the call, we discussed automating data collection using Azure Elastic Jobs.
Elastic Jobs allow you to:
- Schedule DMV snapshots
- Store historical trends
- Correlate spikes with workload patterns
Microsoft provides full guidance on creating and managing Elastic Jobs using T‑SQL here:
Create and manage Elastic Jobs using T‑SQL
Index Management and Class 2 Impact
Index maintenance can indirectly increase Class 2 activity by:
- Increasing version store usage
- Triggering additional background cleanup
Instead of manual index tuning, we recommended enabling Query Performance Insight – Index recommendations in the Azure Portal. This allows Azure SQL Database to automatically:
- Suggest index creation
- Suggest index removal based on real workload patterns.
Why Checkpoints Cannot Be Tuned
A common question is whether checkpoint frequency can be reduced to lower system log activity.
In Azure SQL Database:
- Checkpoints are engine‑managed
- There is no diagnostic or configuration setting to control their frequency
- This design ensures platform stability and predictable recovery behavior
As a result, monitoring—not tuning—is the correct approach.
Practical Takeaways
From this case, the key lessons are:
- Not all transaction log growth is user‑driven
- Class 2 transactions are a normal part of Azure SQL Database
- DMVs provide the best visibility into system behavior
- Trend‑based monitoring is more valuable than point‑in‑time checks
- Automation via Elastic Jobs is essential for long‑term analysis
Conclusion
Class 2 transactions are often misunderstood because they operate quietly in the background. By using the right DMVs and collecting data over time, you can clearly distinguish expected system behavior from genuine workload issues.
If you’re coming from PostgreSQL or on‑prem SQL Server, the key mindset shift is this:
Azure SQL Database exposes outcomes, not internals—and that’s by design.
Microsoft