search

olahallengren / sql-server-maintenance-solution

SQL Server Maintenance Solution
https://ola.hallengren.com
MIT License
2.87k stars 735 forks source link

Issue with MAXTRANSFERSIZE for TDE enabled databases #354

Open amitbhardwaj144 opened 4 years ago

amitbhardwaj144 commented 4 years ago

Description of the issue Issue in backup command for the databases where TDE is enabled.

SQL Server version and edition Execute SELECT @@VERSION SQL Server 2016 Version of the script Check the header of the stored procedure

What command are you executing?

What output are you getting?

Issue in DatabaseBackup.sql

For TDE enabled database in the backup command MAXTRANSFERSize value is coming as 65537, it should be 65336

Here is the mistake line code SELECT @CurrentMaxTransferSize = CASE WHEN @MaxTransferSize IS NOT NULL THEN @MaxTransferSize WHEN @MaxTransferSize IS NULL AND @Compress = 'Y' AND @CurrentIsEncrypted = 1 AND @BackupSoftware IS NULL AND @Version >= 13.04446 THEN 65537 END

olahallengren commented 4 years ago

I have made the code based on this information:

"Moreover, the value of MAXTRANSFERSIZE must be greater than 65536 (64 KB). The minimum value of the MAXTRANSFERSIZE parameter is 65536, and if you specify MAXTRANSFERSIZE = 65536 in the BACKUP command, then compression will not kick in. It must be “greater than” 65536. In fact, 65537 will do just good."

https://techcommunity.microsoft.com/t5/datacat/sqlsweet16-episode-1-backup-compression-for-tde-enabled/ba-p/305296

amitbhardwaj144 commented 4 years ago

Thanks for the wonderful information,

however I was getting below error message while restoring backup which is taken with MAXTRANSFERSIZE =65537 .

"Msg 3241, Level 16, State 40, Line 11 The media family on device ‘XXXXX.bak' is incorrectly formed. SQL Server cannot process this media family."

but at the moment I changed it back to 65536 then restore works fine!!

any clue why this is happening for TDE databases?

olahallengren commented 4 years ago

@AmitBhardwaj144, could you share the exact backup command that DatabaseBackup is generating in your case?

Is there a difference in backup size, when you compare MAXTRANSFERSIZE =65537 with MAXTRANSFERSIZE =65536?

olahallengren commented 3 years ago

however I was getting below error message while restoring backup which is taken with MAXTRANSFERSIZE =65537. "Msg 3241, Level 16, State 40, Line 11 The media family on device ‘XXXXX.bak' is incorrectly formed. SQL Server cannot process this media family."

This looks like a bug in SQL Server. Are you still experiencing this issue? Could you please execute SELECT @@VERSION?