Hi Experts,
Have some question related to large transaction log backups.
We have one big database called "RepositoryDW" which is of 4TB size. It has TDE enabled recently. Backup compression enabled at instance level.
After enabling TDE we see a behaviour like the size of log backups are very large sizeing from 80GB - 160GB. Usually, the size of the .trn files would be 1gb-10GB.
We have log backups scheduled every 1 hour.
Questions
===========
1. Can anyone tell what could the reason why log backups can be so big? open txns?
2. Any way to know what is causing such big log backups ?
Env
===
> SQL Server 2017 EE CU22.
> Windows Server 2019 Datacenter.
> We have this database as part of AG and there are 7 more databases which is part of same AG.
Regards,
Sam
So my off-the-cuff thought is, the answer to both is the TDE. Once you enable TDE, your backups (full, log, and differentials) are encrypted / backing up encrypted data. Encrypted data does not compress very much, if at all.
So potentially, your log backups before, if you weren't compressing them, would've been around the same size as your new, TDEed, backups.
The only for-sure way to know if I'm right or just making stuff up though, would be to either "de-TDE" your database, take a few log backups with compression, then take a few with that turned off, and see if the uncompressed size is similar(ish) to what you're seeing now.
December 29, 2020 at 9:30 pm
You should review the many articles on TDE and backup compression. It seems it will only be enabled if you modify the MAXTRANSFERSIZE on the backup command. There are also some bugs to be worried about...which may or may not impact your system.
https://www.brentozar.com/archive/2016/07/tde-backup-compression-together-last/
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 29, 2020 at 9:50 pm
I stand corrected, I'll be honest, I forgot to look at what SQL version section this was in (I'm still mostly on SQL2014, so compression of TDE is still a non-starter...)
December 29, 2020 at 10:47 pm
I stand corrected, I'll be honest, I forgot to look at what SQL version section this was in (I'm still mostly on SQL2014, so compression of TDE is still a non-starter...)
But you were right - it is all related to TDE and if the OP was on a version less than 2016 (with the right patches) then compression for TDE would be a non-starter. From what I have read - you need that parameter for either full, diff or tlog backups in order for compression to be enabled.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 5, 2021 at 10:58 am
Thank you Jasona and Jeffrey for providing the insights. Many Many thanks.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply