Post reply

Database back up time Fluctuation / Variation

  • June 22, 2012 at 10:51 am

    #257856

    Hi All,

    We have set a Daily full back up on one of our DEV Server.

    The number of databases are 20.

    The back up is schedule as Native SQL maintenance plan.

    It is scheduled in Zero activity time slot on that server.

    The back up time required for all databases varies from 95 - 120 - 130 ~160 mins.

    I have observed / compared the back up file size with previous on all nearly ~same (Slight diffn for some db ~1kb).

    The back up is going to local disk drive.

    We have back up compression and restore verify options are enable as per the requirement.

    I observed the wait types as BACKUPIO, ASYNC_IO_COMPLETION,BACKUPBUFFER, BACKUPTHREAD which are normal as stated on http://msdn.microsoft.com/en-us/library/ms179984.aspx.

    I am not able to locate the exact reason for variation happening in the back up time activity.

    Any suggestion would be helpful.

    Thanks in advance

  • June 22, 2012 at 11:02 am

    #1504635

    I am assuming that the backups are done serially as it is in a maintenenace plan. Could be due to fragmention, both of the databases and where the backup files are being written. Also, it could be differences in the amount log information being backed up each night as part of the full backups.

    You didn't specify what recovery model each of the databases is using, nor if there are other backups being taken, such as t-log backups.

  • June 22, 2012 at 11:06 am

    #1504637

    Some of the delay can also be from the OS as it modifies the backup file to the appropriate size due to over allocation when estimating the size of the backup with compression.

  • June 25, 2012 at 1:01 am

    #1504899

    Thanks Lynn for your response.

    As you asked for the recovery model all DB' are in SIMPLE recovery.

    I will look into the fragmentation issue as pointed by you.

    I just want to ask one more question as you mention in this post that OS might taking long time

    to estimated the size and write the back up file right so is there any solution for this.

    I mean to say is there any way we can speed up this process?

    Thanks in Advance again.

  • June 26, 2012 at 1:12 am

    #1505417

    Did you try with multiple destination backup files? also try to increase BufferCount, MaxTransferSize and BlockSize Parameters.

    Test these scenario in a dev environment before production.

Viewing 5 posts - 1 through 4 (of 4 total)

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