locked
Backup SQL Database to blob storage create 1024GB file RRS feed

  • Question

  • I noticed that during a SQL database backup, a file of 1024 GB is created in the blob storage. At the end of the backup, the space is freed and the .bak file takes its normal size. (140 GB)

    Is this normal?

    Is this behavior documented somewhere?

    We use a maintenance plan (SSIS) to do the backup.


    Thursday, November 15, 2018 8:23 PM

Answers

  • Hello Dan,

    I think this is an expected behavior. The backup process initially reserves space it might think it will require and then later shrinks after the backup completes. You can try testing trace flag 3042 on a test server and then port it to PRODUCTION if all is good.

    Please check this article for more details of what was advised above.


    Regards;
    Vivek Janakiraman
    www.jbswiki.com


    • Edited by Vivek Janakiraman Thursday, November 15, 2018 11:48 PM
    • Marked as answer by Dan Bou Friday, November 16, 2018 2:11 PM
    Thursday, November 15, 2018 11:48 PM
  • Hi Dan Bou,

    As mentioned in INF: Space requirements for backup devices in SQL Server, To get maximum performance benefits during the backup operation, SQL Server calculates the estimated size of the target backup device and initially creates the backup file at that projected size.

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Edited by Teige Gao Friday, November 16, 2018 2:05 AM
    • Proposed as answer by Olaf HelperMVP Friday, November 16, 2018 8:16 AM
    • Marked as answer by Dan Bou Friday, November 16, 2018 2:11 PM
    Friday, November 16, 2018 2:04 AM

All replies

  • Hello Dan,

    I think this is an expected behavior. The backup process initially reserves space it might think it will require and then later shrinks after the backup completes. You can try testing trace flag 3042 on a test server and then port it to PRODUCTION if all is good.

    Please check this article for more details of what was advised above.


    Regards;
    Vivek Janakiraman
    www.jbswiki.com


    • Edited by Vivek Janakiraman Thursday, November 15, 2018 11:48 PM
    • Marked as answer by Dan Bou Friday, November 16, 2018 2:11 PM
    Thursday, November 15, 2018 11:48 PM
  • Hi Dan Bou,

    As mentioned in INF: Space requirements for backup devices in SQL Server, To get maximum performance benefits during the backup operation, SQL Server calculates the estimated size of the target backup device and initially creates the backup file at that projected size.

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Edited by Teige Gao Friday, November 16, 2018 2:05 AM
    • Proposed as answer by Olaf HelperMVP Friday, November 16, 2018 8:16 AM
    • Marked as answer by Dan Bou Friday, November 16, 2018 2:11 PM
    Friday, November 16, 2018 2:04 AM