November 22, 2020 at 2:59 pm
Backup time is almost same and did not reduce after the database size went down by 300 GB.
What could be the reason?
Thanks in advance.
November 22, 2020 at 3:39 pm
Was the 300GB comprised of data which has since been deleted, or just space?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
November 22, 2020 at 4:29 pm
Deleted data and shrink the data files
November 22, 2020 at 6:57 pm
Deleted data and shrink the data files
Understood as to what you've done. Phil's question is what were the conditions before the shrinks? For example, if your database had a file size of 600GB but only 300GB contained any data, then shrinking the file will have no effect on the backup times because SQL Server does NOT backup empty/free space.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 22, 2020 at 10:36 pm
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
November 23, 2020 at 3:56 am
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
Then I agree. That's a problem and I've got no idea why that may be unless it's somehow throttled down by something else. Are you doing it as a single file backup or are you splitting the backup into multiple streams.
Also, 2 hours to backup even 500GB seems like a pretty long time to me. I'm backing up at a rate of about 2TB per hour from my prod box. I have to admit that it's got some pretty hefty connections to it and I'm using backup compression with special settings for buffers, etc, but there shouldn't be that much difference and, most certainly, your backups should not be taking the same time as they were, from what you've described.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 23, 2020 at 3:05 pm
are you producing striped backup files ? ( Backup database - working with a striped media set )
That may also help to reduce the time needed. ( if that is the biggest problem )
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 23, 2020 at 3:07 pm
are you producing striped backup files ? ( Backup database - working with a striped media set ) That may also help to reduce the time needed. ( if that is the biggest problem )
Still, that doesn't explain why the backup durations haven't decreased since the deletion of 200GB of a 500GB database.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 24, 2020 at 4:18 pm
Single File backup
November 24, 2020 at 4:47 pm
Sounds weird. I don't think this is likely just SQL Server. I would start checking to be sure nothing else in infrastructure changed. If you are backing up 200GB now and had 500GB before, it should be faster. I'd also double check that you are measuring things in the same way, before and after. I certainly have been guilty of running one query first, and a different query second.
November 24, 2020 at 5:06 pm
Looking at the backup size, it did not change much just about half the size of what it was before... as we do compress our backups it went from 50 Gb to 25GB and time for backup reduced to just half hour less than what it was before.
November 25, 2020 at 6:47 am
Double check your target folder itself ( or volume ) has not been enabled for compression !
SQLServer does weird things when someone enables compression at disk level.
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 25, 2020 at 12:52 pm
This is a stretch but could it be after your delete that the data is spread all over the disk within the db still so the time it takes to back it up is nearly the same? Do you have the ability to rebuild the indexes on the tables that you deleted data from?
November 25, 2020 at 1:36 pm
Looking at the backup size, it did not change much just about half the size of what it was before... as we do compress our backups it went from 50 Gb to 25GB and time for backup reduced to just half hour less than what it was before.
Ok, the size difference makes sense.
What was the backup time before the delete, and after the delete?
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/
November 25, 2020 at 1:55 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.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply