July 10, 2012 at 12:17 pm
I have 28 DBs on SQL 2008. Four days ago my nightly full backups were taking on average about 3 hours, then suddenly they started taking 5+ hours to complete. I don’t see any huge size increase in any of the DBs, and to be honest, I really don’t know how to analyze what’s causing this. I found I can get 9 days worth of data size, and start-end times of each back up, but again nothing stands out to me why it’s taking so much longer. Does anyone have suggestions on how to analyze what could be causing this extended time? TIA!
July 10, 2012 at 12:32 pm
Try plotting out the time slots the various i/o intensive jobs (backups, index rebuilds, DBCC checks, etc.) occupy, or try a tool like Idera SQL Job Manager, and look for overlaps. Once the DB sizes reach the point where they start to overlap, they can slow each other down and the effect can snowball as additional jobs compete for resources. By spacing them out more maybe you can reduce the total time in which they run.
July 10, 2012 at 1:43 pm
ANy chance you can use backup compression? That should speed up the time backups take.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 11, 2012 at 10:58 am
I'm not a network person but as it was explained to me ... our SAN failed over to another SAN and it so there was a bottleneck going on causing not only my SQLs backup to take forever, but other servers were affected affected as well.
Thanks everyone for your help! For once - not my fault 🙂
July 11, 2012 at 2:15 pm
Shaira (7/10/2012)
I have 28 DBs on SQL 2008. Four days ago my nightly full backups were taking on average about 3 hours, then suddenly they started taking 5+ hours to complete. I don’t see any huge size increase in any of the DBs, and to be honest, I really don’t know how to analyze what’s causing this. I found I can get 9 days worth of data size, and start-end times of each back up, but again nothing stands out to me why it’s taking so much longer. Does anyone have suggestions on how to analyze what could be causing this extended time? TIA!
Are you using a 3rd party backup product?
Can't vouch for other products but with Quest Litespeed when you ramp up the compression ratios the backups can take a lot longer to complete. This is because multiple passes are made over the data looking for better ranges of compression before any pages are backed up to disk. On a large database it can run for sometime.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply