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

Question

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

PJ_SQL

SSCertifiable

Points: 6703
More actions
November 22, 2020 at 2:59 pm

#3812712

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.

Viewing 15 posts - 1 through 15 (of 22 total)

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

PJ_SQL SSCertifiable Points: 6703 More actions · Answer 2

PJ_SQL

SSCertifiable

Points: 6703

November 22, 2020 at 4:29 pm

#3812731

Deleted data and shrink the data files

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

PJ_SQL wrote:

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

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)

PJ_SQL SSCertifiable Points: 6703 More actions · Answer 4

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

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

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

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)

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 6

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

Johan Bijnens wrote:

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

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)

PJ_SQL SSCertifiable Points: 6703 More actions · Answer 8

PJ_SQL

SSCertifiable

Points: 6703

November 24, 2020 at 4:18 pm

#3813516

Single File backup

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 9

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.

PJ_SQL SSCertifiable Points: 6703 More actions · Answer 10

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.

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 11

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

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 12

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?

Michael L John One Orange Chip Points: 27055 More actions · Answer 13

PJ_SQL wrote:

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/

PJ_SQL SSCertifiable Points: 6703 More actions · Answer 14

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.