locked
SQL 2016 CU backup problem RRS feed

  • Question

  • I have Microsoft SQL Server 2016 SP1 CU5 Enterprise Edition.

    Every day I do backup database backup with copy_only. Database have around 900 GB, TDE enabled, recovery mode FULL.

    Two weeks ago I had CU 3.

    I use this script for backup:

    BACKUP DATABASE [DB] TO DISK = N'H:\Backup\DB.bak' WITH COPY_ONLY, NOFORMAT, NOINIT, NAME = N'DB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, BUFFERCOUNT = 2200, BLOCKSIZE = 65536, MAXTRANSFERSIZE=2097152
    GO

    Whit CU 3 backup finished for maximum 20 minutes (measurement period: 29.6.2017. (date of install SQL Server) - 25.9.2017. (date of install CU5)).

    But when I applied CU5 my identical backup finished for around 2 hours, with identical script, same database size, same settings.

    What is wrong, how can I do backup again in 20 minutes?

    Monday, October 9, 2017 12:15 PM

Answers

  • Emphasizing more on what Erland said, please open the Support ticket. Its highly likely you would get solution or bug fix ASAP

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    • Marked as answer by Maty M Friday, October 13, 2017 6:22 AM
    Thursday, October 12, 2017 6:11 AM

All replies

  • Your resulting backup compressed file is it the same size?  You can check from backupset history:

    SELECT TOP 100 * FROM msdb.dbo.backupset bs order by bs.backup_set_id desc;

    If you remove your buffer count parameter are you seeing time's decrease? 
    Monday, October 9, 2017 5:09 PM
  • I note that you have both TDE and compression. For compression to be meaningful they decrypt, comrpess and reencrypt. I don't know exactly how that works, but it can be worth trying the effect of not compressing the backup.

    I assume that you copy the backup elsewhere, so that you can get hold of the backup from last week, would that be needed?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, October 9, 2017 9:41 PM
  • Mighty-O,

    Backup compressed file is the same size with CU3 and CU5.

    If I remove buffer count time is same, with CU5 around 2 hours.

    I installed new server on same environment but with CU3 for test and backup finished for around 20 minutes.

    What is wrong with CU5?

    Tuesday, October 10, 2017 6:57 AM
  • When I backup with COMPRESSION and without time of backup is same.

    I have backup for last 14 days.

    Tuesday, October 10, 2017 7:06 AM
  • I installed new server on same environment but with CU3 for test and backup finished for around 20 minutes.

    That's certainly an indication of that it is an issue in SQL Server. (And not a change in the I/O subsystem that happened to coincide with the upgrade.)

    As this seems to be a regression, I would be inclined to open a case with Microsoft to have this addressed.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, October 10, 2017 9:34 PM
  • How to contact Microsoft?
    Wednesday, October 11, 2017 9:36 AM
  • How to contact Microsoft?
    Your issue has already been reported to MS and you should thank Erland for that. They are looking into the issue I am not saying this is really an issue from there side it could be anything. May be something fixed in CU4 has come up again after applying CU5.

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Wednesday, October 11, 2017 9:48 AM
  • Your issue has already been reported to MS and you should thank Erland for that. They are looking into the issue I am not saying this is really an issue from there side it could be anything. May be something fixed in CU4 has come up again after applying CU5.

    It is correct that I have mentioned this to people at Microsoft and referred them to this thread. However, whether they will act on it, I don't know. Particularly, if this is a pressing issue for you, you should absolutely open a case. From what I know so far, it can still be related to something in your environment. If they can repro the issue themselves, I guess they will look into fix, but how they will prioritise it, I don't know.

    As for how to open a case, my hope was that since you have Enterprise Edition is that you are in an organisation with a support contract. And maybe you are, but you mighthave to talk to your IT guys. Else, Google can help you to find out. I never open cases myself, so I don't really know. What I do know is that you may have to leave credit card information the first thing you do. If the issue proves to be a bug in the product, you will be reimbursed anything you are charged initially.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Proposed as answer by Shanky_621MVP Thursday, October 12, 2017 6:11 AM
    Wednesday, October 11, 2017 10:58 AM
  • I was informed that they did indeed some thing in this area in CU4. While it may not help you to improve your backup times, it can still be interesting to know:

    https://blogs.msdn.microsoft.com/sql_server_team/backup-compression-for-tde-enabled-databases-important-fixes-in-sql-2016-sp1-cu4-and-sql-2016-rtm-cu7/


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, October 11, 2017 7:55 PM
  • Emphasizing more on what Erland said, please open the Support ticket. Its highly likely you would get solution or bug fix ASAP

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    • Marked as answer by Maty M Friday, October 13, 2017 6:22 AM
    Thursday, October 12, 2017 6:11 AM