September 24, 2015 at 1:48 am
We are running Differential back up Every 3 Hours and It's taking almost 2 hours to complete.
Server Configuration is:
Windows server 2008 R2.
RAM : 64 GB and 64 Bit Operating System.
User Database : 1
Database size: 1 TG mdf File. 14 GB .ndf File and 40Gb Log File.
Any Suggestion or help how to minimize the differential back up time.
September 24, 2015 at 1:57 am
Hi,
What is your backup plan?
How frequently do you have full and log backups?
This could be numerous reason for the 'slowness':
the cumulated diff backups have very big size (maybe only weekly full backup is in place?)
too many changes happening in three hours on the db
slow backup device (disk, tape etc)
etc.
________________________________________________________________
"Accept conditions as they exist, or accept the responsibility for changing them."
September 24, 2015 at 2:11 am
Every Monday, Wednesday & Friday Full Back Up Process Run.
Every Sunday Index Rebuild and statistics Update Process Run.
Differential back run every 3 hours.
September 24, 2015 at 2:18 am
schaudhary (9/24/2015)
Every Monday, Wednesday & Friday Full Back Up Process Run.Every Sunday Index Rebuild and statistics Update Process Run.
Differential back run every 3 hours.
Is it a local or remote backup? What kind of storage?
How long is your full backup take?
How big is your latest diff backup file? What was the diff backup time before your '2 hour' problem?
on a side note: did you checked the underlying storage system if this time is acceptable considering the file size?
You could try and use the compressed backup feature (be careful as it is more CPU hungry)
https://technet.microsoft.com/en-us/library/bb964719(v=sql.105).aspx
________________________________________________________________
"Accept conditions as they exist, or accept the responsibility for changing them."
September 24, 2015 at 2:35 am
Is it a local or remote backup? What kind of storage?
Ans: we store back up on network drive.
How long is your full backup take?
Ans: Full back up takes 4 Hours.
What was the diff backup time before your '2 hour' problem?
Ans: Before two hours it's complete in max "4 Mins"
September 24, 2015 at 2:40 am
What is the size of your latest diff backup?
Are you on simple recovery model? (lack of log backups)
Were there any changes in the usage of the database? (4 mins diff vs. 2 hour diffs)
Is the network drive has enough bandwidth at times of the diff backups?
________________________________________________________________
"Accept conditions as they exist, or accept the responsibility for changing them."
September 24, 2015 at 3:05 am
schaudhary (9/24/2015)
Is it a local or remote backup? What kind of storage?Ans: we store back up on network drive.
Assuming that the network connection might be slowing this, do you have enough local storage space for the DIFF backup?
My thought was, as a short-term fix, to backup locally and then copy/move the file to the network location. e.g. using RoboCopy.
September 24, 2015 at 3:20 am
There is not any changes made in database.
and storage on network drive does was not issue because other servers database back up was also stored at network place.
Can you give me the possible scenario which make the differential back slow?
September 24, 2015 at 3:54 am
Why are you running a differential backup every 3 hours? Don't forget, a differential backup backs up every page that has changed since the last full backup, so will only get bigger (and longer) each time until the next full backup runs.
You don't mention any transaction log backups. Are you running any?
September 24, 2015 at 7:09 am
Captain Scarlet has it nailed. Each differential backup is going to take longer and longer because they are cumulative since the last full backup. I'm not sure I'd bother taking so many differentials. Instead, make sure you have log backups in place to be able to restore to a point in time.
To speed up backups though, the best answer, throw disks at the problem. Instead of writing to a single backup file on a single disk, write to several backup files on several disks. This will entail getting all those files back together for a restore though. Also, if you're working with a SAN, be sure that "multiple disks" really is multiple disks and you're not just trying to write to the same disks that the regular backup writes to.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
September 27, 2015 at 4:17 am
Grant Fritchey (9/24/2015)
I'm not sure I'd bother taking so many differentials. Instead, make sure you have log backups in place to be able to restore to a point in time.
We do Log backups every 10 minutes, a full backup on midnight Sunday and a differential backup at midnight on other nights. If we had to restore to 11:59pm it would be 144 Log files to restore which would take "quite some time" 🙂 Our DR policy allows for, and is comfortable, with that but a DIFF every 3 hours, to cut the number of Log files that need restoring, would certainly help. If the Diff backup is taking 2 hours then the Log must?? be pretty significant, so in the event a restore of 23 hours worth of Logs is needed that could be a long time ...
Or perhaps given that length of DIFF backup the system should have a stand-by-server instead of a fast-ish restore process?
September 27, 2015 at 4:21 pm
Doing an index maintenance right after the FULL could reduce the size of the Diff backup.
September 28, 2015 at 2:01 am
vedau (9/27/2015)
Doing an index maintenance right after the FULL could reduce the size of the Diff backup.
What makes you think that would reduce the size of the Diff?
It's likely to have completely the opposite impact, and make the Diff large to start with.
September 28, 2015 at 3:26 am
Sorry I meant to say BEFORE the Full backup.
September 28, 2015 at 3:28 am
vedau (9/27/2015)
Doing an index maintenance right after the FULL could reduce the size of the Diff backup.
I think what you are trying to say is doing maintenance just BEFORE the full backup not RIGHT AFTER. I read about that here when i was setting up ola's maintenance solution (on Scheduling Strategy slide).
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply