Backup time not reducing even after DB size went down 300 GB

  • PJ_SQL wrote:

    Yes , backups are compressed. Before deleting data it was taking around 5 hours ( this is for the all the DBs in the instance), now it is taking lil more than 4 hours.

    Also, indexes are rebuild during the weekend.

    If I am following - this seems to be correct.  You have one database that you reduced in size - and that one database was taking ~2 hours to back up.  Now, your overall backup time is 1 hour less than before...so instead of 2 hours to backup that one database it seems to now be taking 1 hour.

    With that said - I think you have other non-SQL related problems.  Backing up a 500GB database should not take 2 hours...unless you are backing up over the network to a UNC location.

    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

  • Jeffrey Williams wrote:

    PJ_SQL wrote:

    Yes , backups are compressed. Before deleting data it was taking around 5 hours ( this is for the all the DBs in the instance), now it is taking lil more than 4 hours.

    Also, indexes are rebuild during the weekend.

    If I am following - this seems to be correct.  You have one database that you reduced in size - and that one database was taking ~2 hours to back up.  Now, your overall backup time is 1 hour less than before...so instead of 2 hours to backup that one database it seems to now be taking 1 hour.

    With that said - I think you have other non-SQL related problems.  Backing up a 500GB database should not take 2 hours...unless you are backing up over the network to a UNC location.

    Just curious, Jeffrey... where are you getting the 2 hours duration for the original backups?  It hat just a type-o because the OP originally stated the backups were taking 5 hours before his deletes and 4 hours after his deletes.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Earlier in the thread he stated the database was taking 2 hours.

    PJ_SQL wrote:

    Database was 500Gb, 200 GB worth data deleted and then shrink files to reclaim space, backup was taking 2 hours before and still taking same time after database reduced to 200 GB

     

     

    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

  • Jeffrey Williams wrote:

    Earlier in the thread he stated the database was taking 2 hours.

    PJ_SQL wrote:

    Database was 500Gb, 200 GB worth data deleted and then shrink files to reclaim space, backup was taking 2 hours before and still taking same time after database reduced to 200 GB

    Ah... I see what happened.  I was focusing on what you quoted, which is apparently where he changed his tune from the original 2 hours that he posted.  Makes one wonder how he's measuring backup time.

    Thanks, Jeffrey.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I believe Jeffery is onto something. The OP did say "database" (singular) had data deleted and the associated data files shrank by 3/5. The OP then verified that the backup size was reduced by 1/2 (50 GB down to 25 GB).

    The OP then stated this:

    Before deleting data it was taking around 5 hours ( this is for the all the DBs in the instance), now it is taking lil more than 4 hours

    Notice that the OP says that the 5 hours is for "all the DBs". We need the OP to tell us how long that one database took to backup prior to the data deletion and shrink.

     

  • although not a direct help on this you may wish to consider testing doing split file backups (not stripped) to see if performance improves.

    sometimes it does sometimes it doesn't.

    see https://www.sqlservercentral.com/forums/topic/creating-split-backups-question for one short discussion on the subject including times from my own db's

  • I hope you mean the TSQL way for compression!

     backup database xyz ... with compression ;

    Do not use compression at windows operating system level !

    Unless you have many large databases, 4 Hours is way to long

    ( in our case 22 db / 179GB compressed backup -> 30 minutes in total ) ( San ssd lun for backups )

    btw: I also format backup volumes as large as possible

    • This reply was modified 3 years, 4 months ago by  Johan Bijnens.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • As a reference, I have a server with 30 databases whose total size is just over 500 GB.  The backups run to a UNC, and it takes approximately 40 minutes.  I have a dedicated network segment that is only for backups, there is no other traffic on that segment.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply