November 25, 2020 at 9:58 pm
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
November 25, 2020 at 10:39 pm
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
Change is inevitable... Change for the better is not.
November 25, 2020 at 10:45 pm
Earlier in the thread he stated the database was taking 2 hours.
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
November 26, 2020 at 1:55 am
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
Change is inevitable... Change for the better is not.
November 26, 2020 at 10:37 am
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.
November 26, 2020 at 1:59 pm
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
November 27, 2020 at 1:12 pm
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
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
November 27, 2020 at 2:24 pm
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