Forum Discussion
Major increase in transaction log backup size after upgrading to SQL Server 2022
Has anyone experienced a major increase in transaction log backup size after upgrading to SQL Server 2022? I recently upgraded one of our SQL Server environments from SQL Server 2019 to SQL Server 2022 (migrated databases to new SQL Server 2022 instances). After the upgrade, I noticed that transaction log backup sizes increased by 3× to 5×, even though:
- No application changes were made
- Workload and data change volume should be roughly the same
- Backup schedule remained the same (log backups every 30 minutes)
- Backup compression is default (same as before)
- The database is part of an Always On Availability Group running in synchronous commit mode
Metrics I obeserved:
- Transaction log backup file size — increased 3× to 7×
- Log Write Throughput/sec — more than doubled
- Write Log Throughput — increased after upgrade
- Background Write Pages/sec — went up as well
- Log Bytes Flushed/sec
- Bytes Sent to Replica/sec — increased post-upgrade
- Backup/Restore Throughput/sec — increased from ~13 MB/sec → ~38 MB/sec
- Bytes Sent to Replica/sec (Always On counter) — increased
- New hardware is used — previous physical disk sector size was 4K, now it's 512 bytes
- Same Amount of RAM and CPU
3 Replies
This is not uncommon, it has been seen after upgrading to SQL Server 2022. Even if the workload hasn’t changed, SQL Server 2022 introduces a few features that can increase the amount of transaction log activity. One of the biggest contributors is that Query Store is enabled by default on upgraded databases, and that alone can noticeably increase logging overhead unless it’s tuned or disabled. The newer persistent log buffer and changes in how the engine flushes and optimizes writes can also generate more log traffic, especially in Always On environments, since more data is being flushed and replicated more efficiently. Finally, because your new hardware has higher throughput, it’s likely completing more writes in the same time window so the log backups look larger simply because SQL Server can push more work through. In short, nothing is “wrong,” but a combination of new engine behavior plus Query Store plus better Input Output can easily explain the 3x to 5× change. If needed, tuning Query Store or adjusting backup/log settings can help bring it down.
this is the current compression ratio of the log backup files:
wrong info given above - New hardware is used — previous physical disk sector size should be 512 bytes and now it's 4K.
