Backups now taking over 24 hours!

Question

Backups now taking over 24 hours!

anthonystevens

SSC-Addicted

Points: 456
More actions
May 16, 2016 at 6:24 am

#326367

Hi,
I'm hoping someone can help. I've got 65 databases on one SQL instance and I'm running the Ola Hallengren Full backup job each night. Previously this backup took just over an hour to complete but now it's taking around 27 hours! This change happened when our IT department moved the instance from a VM to local disks. They have now moved it back to a VM but the full backups are still taking over 24 hours.
Is anyone able to offer me some advice on where I should start to try and troubleshoot this problem and determine why this job is taking a lot longer than it used to?
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

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

Jez-448386 SSCarpal Tunnel Points: 4570 More actions · Answer 1

We had a similar problem with our VM when it was upsized. The infrastructure guys said that it was "just a config change" but afterwards performance was dreadful. In the end we rebooted the VM and all was good.

Jez

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

People screwed up (in multiple ways). It is just a matter if finding the bottleneck and fixing it and repeating until performance it acceptable. There are quite a number of reasons why backups could be slow. I would first benchmark the write speed from the SQL Server to the backup volume and also benchmark the read speed on the disk that holds the database data file(s) (from the SQL Server itself).

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

DeWayne_McCallie Hall of Fame Points: 3617 More actions · Answer 3

Also, I question the need for a Full backup nightly (maybe the business has a good reason); however, a weekly Full, a nightly Diff and an hourly (or every X minute) TLog backup for each DB works well for most situations.

anthonystevens SSC-Addicted Points: 456 More actions · Answer 4

Looks like it was a problem with our SAN. Thanks for the help and advise.

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

anthonystevens (5/23/2016)
Looks like it was a problem with our SAN. Thanks for the help and advise.

What kind of problem?

Also, why are you wasting valuable SAN space to store backups?

--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)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

Jeff Moden (5/23/2016)
anthonystevens (5/23/2016)
Looks like it was a problem with our SAN. Thanks for the help and advise.
What kind of problem?
Also, why are you wasting valuable SAN space to store backups?

Because that's where all the space is?!? 😀

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

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

TheSQLGuru (5/23/2016)
Jeff Moden (5/23/2016)
anthonystevens (5/23/2016)
Looks like it was a problem with our SAN. Thanks for the help and advise.
What kind of problem?
Also, why are you wasting valuable SAN space to store backups?
Because that's where all the space is?!? 😀

That, of course, could be the only reason. I'm just amazed that anyone would put their backup on the same box as the data especially since SAN storage is a fair bit more expensive than NAS.

--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)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 8

Jeff Moden (5/23/2016)
TheSQLGuru (5/23/2016)
Jeff Moden (5/23/2016)
anthonystevens (5/23/2016)
Looks like it was a problem with our SAN. Thanks for the help and advise.
What kind of problem?
Also, why are you wasting valuable SAN space to store backups?
Because that's where all the space is?!? 😀
That, of course, could be the only reason. I'm just amazed that anyone would put their backup on the same box as the data especially since SAN storage is a fair bit more expensive than NAS.

And I had a client that had a CATASTROPHIC loss of their SAN - TWICE - IN THE SPAN OF 4 MONTHS!!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

anthonystevens SSC-Addicted Points: 456 More actions · Answer 9

We are not backing up to the same box as the data. Backups are being stored offsite as part of our DR. Disk IO on our SAN that holds our VM's was under performing for some reason so after replacing it all now works like a dream 🙂