3

We have recently implemented TDE and backup compression is also enabled. Before TDE backup used to take about 20min but now it takes about one hour.

I have set @MaxTransferSize to 3MB, 2MB, 1MB and 0.5MB but didn't make any difference time wise, database is 90GB of size and it is SQL Server 2016 SP1 CU4.

We using Ola's Maintenance Solution/Scripts to backup the database. Any idea why it is taking 3x longer when TDE enabled please?

  • There were lot of fixes and enhancement related to TDE and backup compression in SQL Server 2016 Sp1 and Sp2. You are at RTM CU4, before anything I would suggest you to apply SQL Server 2016 SP2 ASAP. Please also read Backup Compression For TDE Enabled DB. This blog tells you how to extract maximum when using TDE and backup compression together – Shanky Jun 4 '18 at 11:20
  • @Shanky we aren't at RTM CU4 but SP1 CU4. – Sajid Jun 4 '18 at 11:26
  • Are you backing up on a network share? How big were the backups before TDE and how big are they now? Any other changes in addition to the TDE? – vonPryz Jun 4 '18 at 14:30
  • 1
    By any chance, you are backing up to the same media using INIT but not using FORMAT ? Use FORMAT and see if it is still slow or not. Also, update to latest SP2 since you are using TDE and there was a known bug which corrupts the database FIX: TDE enabled database backup with compression causes database corruption in SQL Server 2016 – Kin Shah Jun 4 '18 at 14:31
  • @Sajid ok, but still I would suggest you to apply SP2. Plus the link I shared has some information which would help you – Shanky Jun 4 '18 at 17:19
0

I managed to reduced the time to 28min now, changed @MaxTransferSize= 524288 to 262144 and also removed @Verify='Y' parameter which did the trick.

I believe backup @Verify option was the real issue here, as soon as I removed the option backup time cut to half.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.